Recovering Unsaved and Unsaved Excel Files (Part 2)

In this article, Mytour will guide you through some other methods to recover unsaved and unsaved Excel files. Additionally, readers can refer to some existing articles on Mytour to learn how to send emails from Excel spreadsheets using VBA Script.

Activate options to recover unsaved Excel files

Excel comes with predefined settings that allow users to recover unsaved and unsaved Excel files. However, it is crucial to ensure that these options are activated.

1. Activate AutoSave and AutoRecover

To ensure that Excel automatically saves files and displays recovery options, we need to activate these settings.

Although these options are activated by default, sometimes we may need to perform this step if the options are not enabled.

Follow the steps below to activate the AutoSave and AutoRecover features in Excel:

Step 1: Click on the File tab.

Step 2: Select Options.

Step 3: In the Excel Options dialog box, click on Save located in the left pane.

Step 4: Ensure the following settings are activated (as shown below):

Among these options:

- Save AutoRecover information every X minutes: This option ensures files are saved every 10 minutes (you can specify any time value if desired).

- Keep the last AutoRecovered version if I close without saving: This option ensures that in case of Excel/system crash, errors, or if we close an Excel file without saving, the saved version can be automatically recovered.

- AutoRecover file location: This is the default location to store automatically recovered files. We can change this location if desired.

Note: If your Excel file contains a series of formulas and charts, setting automatic save every 5 minutes may lead to Excel freezing or hanging. To avoid this situation, we can set a longer time interval.

2. Automatically Create Backup of Saved Excel File

While Autosave and Autorecover options are useful in case we forget to save files or due to system or Excel glitches, automatic backup will create a copy of the file whenever we save it.

For instance, if working with an Excel file named Test.xlsx and we save the file in a folder, it will automatically create a backup copy named Test_backup.xlsx.

From now on, whenever we make changes and save the Test file, these changes will also be saved in the backup.

This can be useful if we want to make some changes in the saved file and wish to revert. In this case, we can use the backup as a replacement.

Or in the event of file corruption or damage, we can use the latest backup file to ensure the latest changes have been saved.

Follow the steps below to activate the auto-backup feature for a workbook in Excel:

Step 1: Click on tab File.

Step 2: Click on Save As.

Step 3: Click on Browse to open the Save As dialog box.

Step 4: In the Save As dialog box, click on the Tools option (located in the bottom-right corner of the dialog box).

Step 5: Click on the General Options.

Step 6: In the General Options dialog box, check the Always Create Backup.

Step 7: Click on OK.

Step 8: Save the file to your preferred location or folder.

Henceforth, every time you save the file, a backup copy will be generated and saved with the prefix 'Backup of'.

Below are some crucial notes on utilizing the auto-backup feature in Excel:

- This pertains to the workbook settings. Meaning, if you activate the settings for one workbook, it functions exclusively for that workbook. To extend the settings to another workbook, manual activation is required.

- This option differs from Autorecover and Autosave options. These are independent settings and configurations that do not impact other settings. Hence, we can work on a workbook with all AutoRecover, AutoSave, and AutoBackup options active.

- Backup files are saved with the extension .XLK. This is the extension for backup files. When opening this file, a prompt will appear; click Yes to open the backup file.

- If you make changes to the main file and save it only once, these changes are not saved in the backup files. If saved again, the changes will be stored in the backup file.

3. Automatically save Time-Stamped copies of Excel workbook

Beyond utilizing the features and options available in Excel, we can also employ short VBA code to ensure the security of our files and data.

With VBA code, we can save a timestamped copy of the Excel file right after saving the file (or closing the file window).

This ensures that we have all previous versions of the Excel file available and can restore the previous file version in case of any errors or mistakes. Additionally, as it is timestamped, we can easily locate the version we want.

Below is the code to create a timestamped copy of the workbook immediately after saving the file:

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False

Dim BackupPath As String

BackupPath = 'C:\Users\sumit\Desktop\Test\'

ThisWorkbook.SaveCopyAs BackupPath & Format(Now, 'dd-mm-yyyy hh:mm:ss') & ' ' & ActiveWorkbook.Name

Application.ScreenUpdating = True

End Sub

Note: In the above code snippet, we replace C:\Users\sumit\Desktop\Test\ with the desired location path for storing backup copies.

Additionally, we can use a similar code to save a copy before closing the workbook (instead of saving a copy each time the file is saved):

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim BackupPath As String

BackupPath = 'C:\Users\sumit\Desktop\Test\'

ThisWorkbook.SaveCopyAs BackupPath & Format(Now, 'dd-mm-yyyy hh-mm-ss') & ' ' & ActiveWorkbook.Name

End Sub

We need to place the code above in the ThisWorkbook code window.

To do this, open the VB Editor (using the shortcut Alt + F11), then double-click on the This Workbook object in the Project Explorer.

4. Fixing Corrupted or Damaged Excel Files

In case your Excel file is corrupted or damaged, you can apply some methods below.

The first and most crucial solution is to utilize the built-in repair options available in Excel.

Follow the steps below to recover data from corrupted or damaged files:

Step 1: Open Excel.

Step 2: Click on File and then select Open (or use the shortcut Ctrl + O).

Step 3: Click the Browse button.

Step 4: In the dialog box displayed on the screen, choose the corrupted or damaged file.

Step 5: Click on the downward arrow icon within the Open button as shown below:

Step 6: Click on Open and Repair.

Step 7: Excel will display a message as shown below. Click on Repair.

If everything goes smoothly, you can recover your data from the corrupted file.

If the Repair option proves ineffective, follow the provided instructions up to step 5. Instead of opting for Repair, choose Extract Data. Excel will prompt you to either convert values or recover formulas.

Feel free to select any option. Excel will attempt to extract the data and generate a new spreadsheet with the extracted information.

In cases where the aforementioned methods are not applicable, we may need to resort to third-party tools to restore corrupted or damaged Excel files.

Here are some approaches to recover unsaved or differently saved Excel files. You can explore the article on how to recover unsaved Excel files part 1 on Mytour to discover its contents. If you have any queries or need clarification, readers can leave their thoughts in the comments section below the article.

Ngoài ra, các bạn có thể tham khảo cách lấy lại file word chưa lưu để khôi phục phần văn bản bị mất đi, đặc biệt là những văn bản quan trọng, cách lấy lại file word chưa lưu được trình bày chi tiết trên Mytour.