Macros in Excel eliminate the need to write the VBA code and automate the tasks that you repeatedly do in the file. Creating macros means that you are recording your keystrokes and mouse clicks. If your macro-enabled Excel file gets corrupted, you’re not able to access the data or even the entire file. In this article, we’ll discuss the causes behind corruption in the Excel file and the solutions to fix the issue and recover the data.
Reasons for Corruption in Macro-enabled Excel Files
There are different causes that may lead to corruption in Excel files. Some of them are:
- Hard drive corruption
- Problematic Microsoft updates
- Virus or malware attack
- Sudden system shutdown
- Application is abruptly closed
- Mishandling of the Excel file
- Unexpected power failure
How to Recover a Corrupted Macro-enabled Excel File?
Follow the given methods to recover corrupted macro-enabled Excel file.
Method 1: Revert to the Previously Saved Version
Excel saves a version of a file automatically if the AutoRecover option is enabled. If your Excel file is closed abruptly while working and gets corrupted, then you can try to restore the previously saved version of that file. To use the Recover Unsaved Workbooks option in Excel, follow these steps:
- Open the corrupt Excel sheet (if it is opening).
- Go to File > Info > Manage Workbook.
- Click on Recover Unsaved Workbooks.
- Choose the available last auto-saved file version and click Open.
- Verify the data.
- Go to File and click Save As to save the file.
Method 2: Set the Calculation Option in Excel to Manual
If you are unable to open the corrupted Excel file, then you can try switching the calculation settings from automatic to manual. This can help in opening the Excel file. The steps are as follows:
- Go to File > New.
- Click Blank workbook under the Available Templates tab.
- Go to File > Options.
- The Excel Options window is displayed. Click the Formulas option.
- Go to the Workbook Calculation section and click the Manual option.
- Click on OK.
- Click on the Open tab in the File menu.
- Choose the desired corrupted workbook and then click on Open.
Method 3: Recover Only the Data from Macro-enabled Excel File
If the Excel file is linked with an external reference, you can use the reference to recover the data, excluding formulas/calculated values. To use the external references, follow these steps:
- Go to File > Open.
- Locate the folder in which the corrupted macro-enabled file is stored.
- Right-click on the file, click Copy, and then click Cancel.
- Go to File > New.
- Click Blank workbook.
- In cell A1 of the new workbook, enter =File Name!A1 and then press Enter. Here, the File Name is the name of the corrupted file that you copied previously.
- The Update Values dialog box is displayed. Select the corrupted Excel file and then click OK.
- If the Select sheet wizard appears, then select the suitable sheet and then click OK.
- Select cell A1.
- Click Home > Copy.
- Select an area that should be of the same size as the range of cells in the corrupted workbook.
- Click Home > Paste.
- Again select the same range of cells, go to Home, and press Ctrl+C.
- Click Home, the arrow under Paste and then click Values.
Method 4: Repair Corrupted Excel File
Microsoft provides a built-in utility – Open and Repair to repair and restore corrupted Excel files. You can use this tool to repair the corrupted macro-enabled Excel file. Here are the steps:
- Go to File > Open.
- In the Open dialog box, choose the corrupted macro-enabled Excel file.
- Click the arrow next to the Open button and select Open and Repair from the dropdown.
- Click on Repair to recover as much data from the workbook as possible.
In case, Microsoft’s in-built Open and Repair utility fails to recover the Excel file, use a professional Excel repair tool, such as Stellar Repair for Excel. It is an advanced tool that can repair severely corrupted Excel files without any file size limitations. It can recover all the objects from the file, including macros, linked lists, formulas, etc. Also, it allows you to preview the recoverable contents in the Excel file before saving.
Closure
Above, we have mentioned the methods to recover corrupted macro-enabled Excel file. You can try changing the Calculation settings from automatic to manual or try the Open and Repair utility. If none of the methods works, then you can use a third-party Excel repair software, such as Stellar Repair for Excel to repair the corrupted Excel file. It can help you recover all the objects from the damaged Excel file with complete integrity. The tool is compatible with Windows 11, 10, 8.1, 8, and 7.
