Turn Off Excel Backups, Temporarily-CodePudding

I am looking for a way, using VBA, to temporarily turn off Excel backups, the one whose default location is "C:\Users[usename]\AppData\Roaming\Microsoft\Excel". I stress temporarily, just for a few minutes. Preferably using VBA, but anything would be considered.

What I am looking for is to turn off these backup, go make some changes, conditional formatting, change VBA code, or something that might take 5-10 minutes. Then when done, turn the backup feature back on.

My problem is this. I want to make some changes on very large excel files. As soon as I start excel decides to do a backup and bingo, I must now wait 5 minutes for it to finish before I can continue.

Sorry, I have no code to show. I don't even know where to begin when tinkering with the inner workings of excel.

CodePudding user response：

To do it manually
To turn it off:
o File >> Options >> Save
o Make a note of Checked state of 'Keep the last autosaved version if...'
o Uncheck 'Save AutoRecover Information every'

To turn it back on:
o File >> Options >> Save
o Check 'Save AutoRecover Information every'
o If checked previously: Check 'Keep the last autosaved version if...''

VBA
To turn it off:
o Application.AutoRecover.Enabled = False

To turn it back on:
o Application.AutoRecover.Enabled = True
o ActiveWorkbook.EnableAutoRecover = True

Simple code to do the trick:

Option Explicit

Private mblEnableAutoRecover as Boolean

Sub ToggleAutoSave()

    With Application.AutoRecover
    
    ''' Save state of EnableAutoRecover and turn both off
        If .Enabled Then
            mblEnableAutoRecover = ActiveWorkbook.EnableAutoRecover
            .Enabled = False
            
    ''' Turn on AutoRecover and reinstate EnableAutoRecover as was
        Else
            .Enabled = True
            ActiveWorkbook.EnableAutoRecover = mblEnableAutoRecover
        
        End If
    
    End With

End Sub

CodePudding user response：

Under File - Options - Save - Autorecover File Location you find the path you described. If you change the Roaming part of the location to Local (eg. "C:\Users[usename]\AppData\Local\Microsoft\Excel"), you will still have backup, but hopefully not over the network. Not the answer to your question as you stated it, but it might be worth a try.