Disable Autorecover for workbooks I open

[jomili]

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?

[nigelog]

Please Login or Register  to view this content.

[MarvinP]

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.

[jomili]

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.

[nigelog]

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

[xlnitwit]

Hi,

You need the Workbook.EnableAutoRecover property. How should the code determine which workbooks you want to do it for?

[jomili]

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.

[jomili]

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.

[xlnitwit]

Are they simple hyperlinks?

[jomili]

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.

[jomili]

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

[shg]

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 ...

[shg]

Before I delete this:

Please Login or Register  to view this content.

[jomili]

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.

[shg]

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

[shg]

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

[jomili]

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.

[jomili]

Figured it out.

Please Login or Register  to view this content.

[jomili]

Full code, thanks to SHG's valuable contribution:

Please Login or Register  to view this content.

[xlnitwit]

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.

[jomili]

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.

[xlnitwit]

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.

[jomili]

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

[xlnitwit]

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.

[jomili]

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.

[xlnitwit]

FileLen actually takes the workbook path so

Please Login or Register  to view this content.

[jomili]

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.

[xlnitwit]

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

[jomili]

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.

[xlnitwit]

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.

[jomili]

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.