+ Reply to Thread
Results 1 to 25 of 25

file is in use error when auto saving with macro

  1. #1
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    file is in use error when auto saving with macro

    Hi all,

    I have got a workbook with several queries that auto refresh every 5 min. After the refresh the file is saved by the macro. Sometimes i will check the file to see if its still function and i will see the error: file is in use try again later. where i have to press ok to have the routine continue.

    This is always open on a remote desktop. other users will get data though this file. When they open their dashboard the data will be update through: update values.

    My guess is that the error occurs when a user updates their values at the same time the macro is trying to save the workbook with the queries.

    Does anyone know a solution for this. i.e. have the routine continue after the error. I already added application display alerts and on error resume next but it does not help.

    The only thing i can think about is having the file close and reopen itself on a set interval?

    Any help would be appreciated.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    If you have other users accessing this file only for reading I would advise you to add the option to open it read-only.
    You can set this when saving the workbook under Options, you can even add a password for opening in edit mode, but then you will have to include this in your macros too.
    Succes Wijand
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    thanks for your response Keebellah,

    The other users do not open the file. The have a link to it. When they open their sheet the values are automatically updated (via edit links, update values) with workbook open routine.

  4. #4
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    would there be any other cause for this error to occur?

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    I'm not sure if it's possible but maybe somewhere in the link you can manage how the file is addressed. Is it SharePoint or just a network share?

  6. #6
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    It are Excel files link to each other bit stored on different servers.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    Yes, but are these Sharepoint servers or just network shares.
    With Sharepoint (in some cases) you need to use the CheckOut function.
    Syntax is something like:

    Please Login or Register  to view this content.
    I found the above code with Google

  8. #8
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    okay, it are network shares.

    i am not sure what the checkout function will do. does it check if the file can be saved at that moment?

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro


  10. #10
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    Thanks again,

    looked it up but it doesnt seem a solution to my problem. i just need to either make sure the vba coding prevents the error, or that it can continue after the error.

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    The you should add error control
    Please Login or Register  to view this content.
    This link helped me and gave a lot of ideas

    http://www.cpearson.com/excel/errorhandling.htm
    Last edited by Keebellah; 06-17-2016 at 10:08 AM. Reason: Forgot something :)

  12. #12
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    I am afraid this does not work either because the error seems to occur outside of VBA. When attempting to save the file, while is it being used by a user to pull data, the error will show, file in use try again later. which is not in VBA. therefore I think the error handler wont capture this error.

  13. #13
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    This is the save part of the code


    Please Login or Register  to view this content.

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    @Wijand:

    I have this part of the code in a module that always worked, okay, I did not build in a retry but based upon this you could do that with a loop counter to stop retrying after x number of attempts:

    Please Login or Register  to view this content.
    Hope this helps some.

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro


  16. #16
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    Keeballah thanks for your help again.

    The workbook I am trying to save is open because that is where the automated refreshing takes place. To have the refreshed data pull through to other workbooks, the open workbook needs to be saved. Therefore the loop of refreshes and save. There is no need to open a workbook because it is already open.
    I just need to save the open workbook which goes fine most of the time, except for the few time the errors occurs. (as I tried to clarify before: my thinking is this happens because another user is trying to pull the data from the open file with the loop, via update links, at the same time the code is trying to save)

    I have found very little applicable information on the internet about this specific issue, but I would imagine other people have encountered this with a file that always stays open and is refreshed and saved on set intervals, and where data is being pulled from to other workbooks via update links.

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    An open workbook which remains open cannot be accessed for editing.
    Unless you have to keep it opened, you won't get it to work.
    I do not understand why it has to remain opened for editing at all times, but you know that the best.
    If that is the only option you have to refresh it, well, you need another strategy.

  18. #18
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    An open workbook which remains open cannot be accessed for editing.
    Unless you have to keep it opened, you won't get it to work.
    I do not understand why it has to remain opened for editing at all times, but you know that the best.
    If that is the only option you have to refresh it, well, you need another strategy.

  19. #19
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    It is just a workbook containing a few queries that are refreshed every x minutes and saved every x minutes. So that other workbooks always have the most updated data whenever the users open their workbooks.

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    Okay, but in between these minutes, is the file closed or do you keep it open?
    How many are those x minutes?
    How long does it take to refresh the queries?
    I say this because you could have a macro (in another workbook) that triggers the open, update save and close so the workbook is only 'blocked' during the time it takes to update.

  21. #21
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    Refreshing of the queries is every 5 minutes and the macro that saves runs every 5.05 minutes. The file remains open 24/7. The queries refresh within 30 seconds more or less.

    The data needs to be updated on short interval for the user to work with it in a efficient way. If they make a change in the financial system the data should show that within a short time to not delay them.
    Last edited by wijnand; 06-23-2016 at 05:00 AM.

  22. #22
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    Then I think this is really a problem for you.
    The only thing I can think of is to NOT update the dashboard via update but a macro button that does that and opens the source file read only, retrieves the data and closes it for the user.
    In this way the user will always have the contents of that moment and it will not hinder the macro running every 5 minutes.
    You could even add a field to the dashboard that updates a timestamp indicating the date and time the user pressed the button.
    Maybe an idea to consider?

  23. #23
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    Actually this was previous setup, a macro in the dashboard would open and close the file but this takes much longer than update values. So for user friendliness I switched to the auto refresh with update values to retrieve the data. Only afterwards I noticed that the error occurs sometimes.

    My only other solution would be to make the file close and open on set interval to make the error go away if it had occurred.

  24. #24
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: file is in use error when auto saving with macro

    I still think that replacing the data update (refresh) be replaced by a macro button will work. Yes, there may be some lag but people won't be updating every 5 minutes.
    If a file is addressed only to read data then this would be the best of both worlds, but... it's your system.
    Happy coding

  25. #25
    Registered User
    Join Date
    02-23-2015
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    28

    Re: file is in use error when auto saving with macro

    Thanks, I will try that.

    Appreciate you're thinking along with me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Error message on saving file after running macro
    By krish2503 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-15-2016, 10:41 AM
  2. File saving error
    By Ravana in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-10-2015, 09:38 AM
  3. Error in opening file and saving a file
    By uday1969 in forum Excel General
    Replies: 1
    Last Post: 11-03-2012, 06:21 PM
  4. Auto Saving a file in a macro to a shared drive
    By Slobey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2008, 02:01 AM
  5. Auto Saving Via Macro To Specific File?
    By stevesunfold in forum Excel General
    Replies: 4
    Last Post: 04-11-2008, 11:57 AM
  6. Auto Saving File to Folder with Current Date
    By mengghee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2008, 05:50 PM
  7. Macro for saving file on Auto Date
    By Prakash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2005, 12:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1