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?

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.

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.

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

Hi,

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

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.

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):

Dim WB as Workbook
Set WB = Workbook that opens after target cell is clicked

Are they simple hyperlinks?

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.

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

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

Before I delete this:

Option Explicit

Dim WithEvents app  As Excel.Application

Private Sub Workbook_Open()
  Set app = Application
End Sub

Sub x()
  app_WorkbookOpen ThisWorkbook
End Sub

Private Sub app_WorkbookOpen(ByVal wkb As Workbook)
  With wkb
    If MsgBox("Autorecover is " & _
              IIf(EnableAutoRecover, "ENABLED", "DISABLED") & " for " & .Name & _
              IIf(EnableAutoRecover, ". DISABLE?", ". ENABLE?"), vbYesNo) = vbYes Then _
       .EnableAutoRecover = Not (.EnableAutoRecover)
  End With
End Sub

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.

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

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

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?

Dim WithEvents app As Application

Private Sub Workbook_Open()
Workbooks.Open _
            ("C:\Users\" & Environ("USERNAME") & "\Documents\My Excel Addins\MyLinks.xlsm"), ReadOnly:=False, UpdateLinks:=True
    Set app = Application
End Sub

Private Sub app_Workbookopen(ByVal Wb As Workbook)
If Wb.Name <> "MyLinks.xlsm" Then
    MsgBox "Just opened: " & ActiveWorkbook.Name
End If
End Sub

Full code, thanks to SHG's valuable contribution:

Dim WithEvents app As Application
Private Sub Workbook_Open()
'Open my Links page and backup my Personal
    Workbooks.Open _
            ("C:\Users\" & Environ("USERNAME") & "\Documents\My Excel Addins\MyLinks.xlsm"), ReadOnly:=False, UpdateLinks:=True
    Const sBak        As String = "C:\Users\LINDSEJ\Documents\My Backups\Personal.xlsb."
    Me.SaveCopyAs sBak & Format(Now(), " mmm d yyyy, hh.mm.ss AMPM.bak")
    Me.Save

    'Add my right click menu for pivots
    FilterPivot_AddRightClick
    'Note the workbooks I open so I can turn off autosave
    Set app = Application
End Sub



Private Sub app_Workbookopen(ByVal Wkb As Workbook)
If ActiveWorkbook.Name <> "MyLinks.xlsm" Then
    With Wkb
    If MsgBox("Autosave is " & _
              IIf(EnableAutoRecover, "ENABLED", "DISABLED") & " for " & .Name & _
              IIf(EnableAutoRecover, ".  DISABLE?", ".  ENABLE?"), vbYesNo + vbInformation, "Turn Autosave On or Off") = vbYes Then _
       .EnableAutoRecover = Not (.EnableAutoRecover)
  End With

End If
End Sub

Figured it out.

Private Sub app_Workbookopen(ByVal Wb As Workbook)
If ActiveWorkbook.Name <> "MyLinks.xlsm" Then
    MsgBox "Just opened: " & ActiveWorkbook.Name
End If
End Sub

FWIW, I would recommend using

If Wkb.Name <> "MyLinks.xlsm" Then

rather than

If ActiveWorkbook.Name <> "MyLinks.xlsm" Then

My apologies for not revisiting this thread earlier but I completely lost track of it. I am glad to see you have a resolution.

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.

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.

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

That is interesting. I wouldn't have expected the event to trigger for the workbook that initiates the event handler. I'd suggest

Private Sub app_Workbookopen(ByVal Wkb As Workbook)
If Wkb.Name <> "MyLinks.xlsm" And Not Wkb Is ThisWorkbook Then
    With Wkb
    If MsgBox("Autosave is " & _
              IIf(EnableAutoRecover, "ENABLED", "DISABLED") & " for " & .Name & _
              IIf(EnableAutoRecover, ".  DISABLE?", ".  ENABLE?"), vbYesNo + vbInformation, "Turn Autosave On or Off") = vbYes Then _
       .EnableAutoRecover = Not (.EnableAutoRecover)
  End With

End If
End Sub

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:

Private Sub app_Workbookopen(ByVal Wkb As Workbook)

MsgBox FileLen(Wkb)
If Wkb.Name <> "MyLinks.xlsm" And Not Wkb Is ThisWorkbook Then

    With Wkb
    If MsgBox("Autosave is " & _
              IIf(EnableAutoRecover, "ENABLED", "DISABLED") & " for " & .Name & _
              IIf(EnableAutoRecover, ".  DISABLE?", ".  ENABLE?"), vbYesNo + vbInformation, "Turn Autosave On or Off") = vbYes Then _
       .EnableAutoRecover = Not (.EnableAutoRecover)
  End With

End If
End Sub

FileLen actually takes the workbook path so

MsgBox FileLen(Wkb.FullName)

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.

Private Sub app_Workbookopen(ByVal Wkb As Workbook)
'Check out autosave settings for Source files I open
'Only look at the source files folder
If Wkb.Path = "\\12aust1001fs01\SHARE10011\Budget\SOBUDGET\_Protected_Data\Source_Docs\_OOE_MOF_PivotTables" Then
    With Wkb
    If MsgBox("Autosave is " & _
              IIf(EnableAutoRecover, "ENABLED", "DISABLED") & " for " & .Name & _
              IIf(EnableAutoRecover, ".  DISABLE?", ".  ENABLE?"), vbYesNo + vbInformation, "Turn Autosave On or Off") = vbYes Then _
       .EnableAutoRecover = Not (.EnableAutoRecover)
  End With

End If
End Sub

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

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.

Do you mean you want to disable it automatically, or still prompt for it? For the latter, simply

If .Enableautorecover then 
If MsgBox("Autosave is ENABLED for " & .Name & "DISABLE?", vbYesNo + vbInformation, "Turn Autosave On or Off") = vbYes Then .Enableautorecover = False
End If

Perfect! Final code (at least until I go back and tinker with it later if I'm so inclined).

Private Sub app_Workbookopen(ByVal Wkb As Workbook)
'Check out autosave settings for Source files I open
'Only look at the source files folder
If Wkb.Path = "\\12aust1001fs01\SHARE10011\Budget\SOBUDGET\_Protected_Data\Source_Docs\_OOE_MOF_PivotTables" Then
    With Wkb
    If .EnableAutoRecover Then
        If MsgBox("Autosave is ENABLED for " & .Name & "." & vbCrLf & vbCrLf & "DISABLE?", vbYesNo + vbInformation, "Kill Autosave?") = vbYes Then .EnableAutoRecover = False
    End If
  End With

End If
End Sub