+ Reply to Thread
Results 1 to 31 of 31

Disable Autorecover for workbooks I open

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Disable Autorecover for workbooks I open

    I know that for my workbooks I can turn off Autorecover, and for my Excel as a whole I can reset the Autorecover timing (I've got mine set at 45 minutes instead of the default 10). However, I constantly have open various workbooks created by another team. These workbooks are huge, and autorecover is a 10 minute process when I have 2 or three of them open.

    These workbooks are updated by another team each morning, and then that team saves them for our use. We can't save changes to the file, and there's no need to have Autorecover running on them. But that team refuses to change their autorecover settings.

    So, is there a way that I can open these workbooks and turn off autorecover for them while they're opened by me?
    I know I'm not stupid, but I suspect I'm a lot stupider than I think I am

  2. #2
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,186

    Re: Disable Autorecover for workbooks I open

    Please Login or Register  to view this content.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

    Re: Disable Autorecover for workbooks I open

    Hi jomili,

    On the site:
    https://support.microsoft.com/en-us/...tions-in-excel

    it claims autorecover only starts when you edit a cell. Can you change your AutoCalculate settings so no cells change?
    It also only autorecovers on office type documents. Might there be some way to rename the imported files to other than .xlsx? This might stop autocorrect.??

    I like nigelog's answer a lot better than mine.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    That will turn off autosave for all the workbooks I have open. I only want to turn off autosave for the big source files I have open, so need to be able to do it at the workbook level. There is an option in the Options/Save field to turn off autosave for the activeworkbook. I'm trying to figure out how to do that programmatically.

  5. #5
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,186

    Re: Disable Autorecover for workbooks I open

    Could you not have a personal.xlsb workbook that opens automatically with excel that is set to not run autocalculation for any workbook you open with that option set. Could you not call by macro before opening the big workbooks??
    I control a few specific options as additions to the right click option menu
    Last edited by nigelog; 07-27-2017 at 11:40 AM.

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Disable Autorecover for workbooks I open

    Hi,

    You need the Workbook.EnableAutoRecover property. How should the code determine which workbooks you want to do it for?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    I have a Links workbook I use to open these files. Right now the link is just a link to the workbook, but I could tie it to a "Workbook.Open" macro to embed the code in. Or I could use a Worksheet Event routine so if my target is one of a certain set of links Autosave would turn off for that workbook. I think that would be better, but I don't know if I can configure that today, as I'm scheduled into back-to-back meetings today.

  8. #8
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    Not sure how to do this via the link on my links page. I know how to capture if the link cell is clicked, but not how to distinguish the workbook being opened as a result of that cell being click, as in (pseudocode):
    Please Login or Register  to view this content.

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Disable Autorecover for workbooks I open

    Are they simple hyperlinks?

  10. #10
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    Yes, no vba behind them, just straightforward hyperlinks.

    I COULD change them to macros, so I could add the Workbook.EnableAutoRecover property to each macro, but if possible I'd rather do through a single macro that fires when a link is clicked.

  11. #11
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    Still no ideas? I'm still a problem in need of a solution (ask my wife, she'll testify to that).

  12. #12
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Disable Autorecover for workbooks I open

    There is also a workbook.enableautorecover property. You could use the application_workbookopen event in Personal to prompt you whether you should turn it off, or do it automatically if opened from a network share, or ...
    Last edited by shg; 08-21-2017 at 03:43 PM.
    Entia non sunt multiplicanda sine necessitate

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Disable Autorecover for workbooks I open

    Before I delete this:

    Please Login or Register  to view this content.
    Last edited by shg; 08-21-2017 at 04:46 PM.

  14. #14
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    Hmmm, So the first Workbook_Open event would be for when my PERSONAL opens, right? If so, I'm not sure how to deploy the Sub x(). For the Private Sub app_WorkbookOpen, I see how it works, but not how to implement it.

    In my case PERSONAL would always be open (call it WB1), I would be clicking a link in another workbook (call it WB2) to open yet another workbook (call it WB3). So it's WB3 I would want to suppress autosave for. WB1 and 2 are already at my default.

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Disable Autorecover for workbooks I open

    It will fire for any workbook that opens. You need a way to distinguish, as suggested in post#12.

  16. #16
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Disable Autorecover for workbooks I open

    If you can distinguish based on file size, use FileLen().

  17. #17
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    Thanks shg, I think you've shown me the right way to go, but I have an issue:

    When I open Excel, it opens my Personal, and my Personal opens MyLinks.xlsm, the workbook that contains all my links. I don't want to alter the autosave in MyLinks, so need to avoid it triggering the macro. So, to test the concept, in PERSONAL, in the ThisWorkbook module, I put the following code. The problem is that when I open Excel I get the message "Just Opened MyLinks.xlsm". How do I configure it to avoid MyLinks.xlsm?
    Please Login or Register  to view this content.

  18. #18
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    Figured it out.
    Please Login or Register  to view this content.

  19. #19
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    Full code, thanks to SHG's valuable contribution:
    Please Login or Register  to view this content.

  20. #20
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Disable Autorecover for workbooks I open

    FWIW, I would recommend using
    Please Login or Register  to view this content.
    rather than
    Please Login or Register  to view this content.
    My apologies for not revisiting this thread earlier but I completely lost track of it. I am glad to see you have a resolution.

  21. #21
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    xlnitwit,
    I agree, but see post 17; that's why I changed to ActiveWorkbook. Seems to be working okay so far, but I'm open to trying something else.

  22. #22
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Disable Autorecover for workbooks I open

    I think that's because you tested Wb.Name but then reported Activeworkbook.Name in that code. You should use the same workbook for both.

  23. #23
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    If I change ActiveWorkbook to WKB, the message I get upon opening Excel is "Autosave is ENABLED for Personal.xlsb. DISABLE?

  24. #24
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Disable Autorecover for workbooks I open

    That is interesting. I wouldn't have expected the event to trigger for the workbook that initiates the event handler. I'd suggest
    Please Login or Register  to view this content.

  25. #25
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    And your suggestions works! Thanks.

    Another issue; based on SHG's FileLen suggestion. Testing it like this I get an error on the FileLen code:
    Please Login or Register  to view this content.

  26. #26
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Disable Autorecover for workbooks I open

    FileLen actually takes the workbook path so
    Please Login or Register  to view this content.

  27. #27
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    And of course in the end I go a whole different route. I decided rather than file size I would go after a specific set of workbooks, those that are saved as our "source" workbooks, most of which are huge, all of which are protected so we can't save changes, so no reason to have autosave turned on for them. Amended code is below.
    Please Login or Register  to view this content.

  28. #28
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Disable Autorecover for workbooks I open

    In your IIF statements, you should use .EnableAutoRecover rather than just EnableAutoRecover

  29. #29
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    Thanks, I didn't catch that in this instance, but you're right, it should have the periods just like in the line right before the End With. Good catch.

    Do you have some insight on changing the Msg? I really only want to check for if it's enabled. If so, I want to disable it. I'm working on other projects so haven't tackled that piece of the code yet.

  30. #30
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Disable Autorecover for workbooks I open

    Do you mean you want to disable it automatically, or still prompt for it? For the latter, simply
    Please Login or Register  to view this content.

  31. #31
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,435

    Re: Disable Autorecover for workbooks I open

    Perfect! Final code (at least until I go back and tinker with it later if I'm so inclined).
    Please Login or Register  to view this content.

+ 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. Is there vba code which can disable/enable switching between open workbooks
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2016, 05:37 AM
  2. Replies: 1
    Last Post: 03-31-2016, 02:06 AM
  3. [SOLVED] disable autorecover
    By Daniel M in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2006, 06:50 AM
  4. Disable AutoRecover
    By Daniel M in forum Excel General
    Replies: 0
    Last Post: 08-11-2006, 05:00 AM
  5. [SOLVED] How to disable AutoRecover feature in Excel
    By Vijay in forum Excel General
    Replies: 1
    Last Post: 03-03-2006, 11:00 PM
  6. Duplicate files open from autorecover
    By Wendy in forum Excel General
    Replies: 7
    Last Post: 11-07-2005, 04:45 PM
  7. [SOLVED] Duplicate files open from autorecover
    By Wendy in forum Excel General
    Replies: 1
    Last Post: 11-06-2005, 06:45 PM

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