AutoSave in Excel

Autosave Function in Excel

Autosave in excel is a function/facility used to automatically save the changes or progress of work. This will help us to reduce the impact of risk in data loss in case of any crash situation in the system. We can find this feature in the options tab under the save section, if we input the time as 5 minutes excel will automatically save our workbooks in every five minutes.

How to Turn On AutoSave in Excel? (Step by Step)

Usually, Autosave is enabled in Excel by default. Let’s see how to Turn on Autosave options in Excel.

• Step -1: Go to the “File” menu. Click on “Options”

• Step -2: Excel options will pop out with many options to manage as per our necessity. Click on the “Save” option – This will take us to customize the workbooks that are saved.

• Step 3 – From this window, we can see the “Save AutoRecover information every ­­­__ minutes” is enabled. This is where we are notifying excel to save the workbook for every n minutes (n could be any number representing no-of minutes in time). By default, Excel will take 10 minutes to perform AutoRecover, but we can change this time as per our requirement, such as 1,2,3 minutes.

• Step 4 – If you enable it and couldn’t able to save, then it will be stored in this path “C:\Users\aa\AppData\Roaming\Microsoft\Excel\” (this path varies for everyone as their recover
  excel autosave location will be different. I request all to check for this autosave location in Excel options – Save.

We can also change the folder to save the backups by entering a different path into the “AutoRecover file location.”

• Step 5 – We can select a specific format when we are saving our workbooks automatically by selecting the format from the list of excel formats that are available.

By default, the format would be “Excel Workbook (*xlsx).”, as this was used by most of the users. And also, make sure we turn on the “Keep the last autosaved excel version if I close without saving.”

• Step 6 – We also can avoid saving the workbooks in one drive-by enabling “Save to Computer by default” as follows:

Till now, we had learned how to turn on autosave in excel, time to be given to performing the task periodically, and many other features that we got.

How to Recover the AutoSaved File?

Now, let’s see how to recover the file, which is got auto-saved. As you can observe, the file path that we had given in the “AutoRecover file location” copy the path.

Step 1 – Now, open the Microsoft Explorer; at the top of the bar, we should paste the path that we had copied from the “AutoRecover file location” and then press ENTER.

• Step 2 – A window with files & folders that are auto recovered will be displayed. From this window, we can click on the file that we want to be recovered, and then you can keep the changes and save the workbook with a new name. Once you enter the path, the below window will pop out.

From here, we can recover the files which are not saved, and our work & time doesn’t go in vain by enabling the autosave in excel.

How to Turn on Autosave by Visual Basic Application (VBA)

Let’s see how can code using Excel Macro will help us to perform autosave in the excel workbook.

• Step 1 – Open “Visual Basic Editor” from the Developer tab in your excel as below.

• Step 2- When we click on the “Visual Basic,” we can visualize a VBA editor getting pop out. This where we have to write code to perform the autosave operation.

• Step 3 – Here, we are not going to create any module for our operation to perform as this is an “Event” where we are going to write a macro. An event is anything like if we open a workbook, then it will be considered as an event; similarly, if we made changes in the workbook, then it is also an event, closing the workbook is also an event, and so on.
• Step 4 – For the autosave operation, we assume “closing the workbook” as an event. Open “ThisWorkbook,” which is on our left side of the page, and start writing a macro.

• Step 5 – As we open the “Thisworkbook,” we can see the “General” and change it to “Workbook” as follows.

• Step 6 – When we change to a workbook, It gives an option to write a macro in an event “Workbook_Open().” There is a list of declarations that we can use to create an event on our right side. Here we will perform a task for autosave in excel, so we have to change the event to “Beforclose.”

• Step 7 – Select “BeforeClose” and start writing the code for autosave in excel.

• Step 8 – “ThisWorkbook.Save” will automatically save the workbook and save it as a macro-enabled workbook to run the code as below:

• Step 9 – Save this workbook as a macro-enabled and close the workbook. Then we can observe that display alert asking for save or don’t save won’t get pop out, but some other warning alert will pop out which is as below:

Step 10 – Once you click on Ok, the workbook with changes will get autosaved. We can also disable this alert by using some code in VBA, which saves a workbook automatically in a simple way.

Let’s see how it works:

These warning messages will come through an application, so we take “Application.DisplayAlerts = False.” This means any display alert will not pop out once this line is included in the code. But it also important to include a line of “Application.DisplayAlerts = True” as this helps when something important displays alert to pop out.

Now try to make changes and close the workbook, we can observe that none of the display alerts will pop out, but changes will get saved. This operation will help us to save our work though we unexpectedly close the workbook without saving it.

Things to Remember

• Autosave in Excel is a splendid feature that saves time and data by saving it on a periodic basis.
• As once we enable the autosave in Excel, we can’t close without saving, which may mislead though we don’t want to save the data.
• It always monitors the user’s work and updates the changes in content to share point or one drive.
• It also allows multiple people to edit the document simultaneously.

Recommended Articles

This has been a guide to what is Autosave in Excel File. Here we discuss how to turn on Autosave in Excel using Excel Options and VBA (Visual Basic Application) and examples. You may also look at these useful functions in excel –

• Excel AutoRecover
• File Extensions in Excel
• Save as in excel
• OneDrive Excel