Macros are a set of instructions that users create in MS Excel using VBA (Visual Basic for Applications). These instructions automate repetitive processes, such as formatting cells, creating reports, etc., by recording keystrokes and clicks. This automation simplifies the workflow as you can easily execute the action in a single click.
When a user creates and saves a macro-enabled Excel file using MS Excel 2007 or later, the file extension changes from .xlsx to .xlsm.
However, sometimes, a macro-enabled excel file (.xlsm) gets corrupted due to various reasons, such as malware infection, abruptly closed MS Excel application, and more. This prevents users from opening the workbook or shows error messages upon opening it.
Let’s read further to learn the reasons behind macro-enabled Excel file corruption and methods to recover data.
Reasons Behind Macro-enabled Excel File Corruption
Macro-enabled Excel files get corrupted due to –
- Bad sectors on the hard drive
- MS Excel application closed abruptly
- Malware attacks
- Abrupt system shutdown
- Problematic MS Office updates installed
Methods to Recover Macro-Enabled Excel Files
If you have a macro-enabled Excel file (.xlsm) that you are unable to open or access data in, you can use the methods explained below to recover its data.
Method 1: Revert to Previous Version
MS Excel comes with a useful AutoRecover option, which, if enabled, automatically saves file versions when MS Excel is closed abruptly or the system shuts down abruptly. These versions can be used to restore excel file to its working condition and recover its data. If you have enabled the AutoRecover option in MS Excel, use it to recover the previous version of your corrupted macro-enabled Excel file. Here’s what to do –
- Open the problematic Excel workbook.
- Click on File from the menu bar.
- Click on Info in the left panel and then click on Manage Workbook.
- Select the Recover Unsaved Workbooks option.
- Choose the recently saved auto-save file version and click Open.
Verify the data and check if the applied formulae are working. After verifying the workbook, click Save As to save the restored xlsm file at a different location.
Note – In case, you haven’t enabled the AutoRecover option but have created a manual backup of the corrupted Excel file on a storage medium, restore it.
Method 2: Switch Excel Calculation to Manual
When you open an Excel workbook with various formulae applied, it takes time to recalculate the formulae and data. This process takes time. But if the Excel has stalled while opening a workbook for a long time, you can change the calculation settings from Automatic to Manual. Doing this should help Excel load the selected workbook faster. To do this –
- Launch MS Excel.
- Click on the Formulas tab in the ribbon.
- Click on Calculation Options and select Manual.
After making the selection, try to open the problematic macro-enabled Excel file. Now, you will have to manually recalculate the workbook. For this, you must go to the Formulas tab > click on Calculate Now.
Method 3: Use External References to Recover Data from a Corrupted Excel File
If you have a corrupt macro-enabled Excel file linked with another workbook or worksheet in the same workbook, you can use external references to recover its data. However, it will not recover formulae or calculated values. Follow the steps below to use references –
- Click on File from the ribbon.
- Click on Open > Browse.
- Locate the corrupt macro-enabled Excel file on your system.
- Right-click on the file, click Copy, and then click Cancel.
- Again, click on File > New.
- Select the Blank workbook option.
- In cell A1 of the new workbook, enter =File Name!A1 and press Enter. Here, the File Name is the name of the corrupted file that you copied previously.
- The Update Values dialog box will appear. Select the corrupted Excel file and click OK.
- You will see the Select Sheet wizard appear. Select the appropriate sheet and click OK.
- Select cell A1.
- Click on Home > Copy. You can also use the CTRL + C shortcut.
- Select an area, starting in cell A1, approximately the same size as the range of cells containing data in the corrupted workbook.
- Click Home > Paste or use the CTRL + V shortcut.
- Once you have selected the range of cells, click on Home > Copy or press CTRL + C shortcut.
- Click Home > the arrow below Paste, and under Paste Values, click Values.
This will remove all the links to the corrupted workbook and will paste the data.
Method 4: Use Open and Repair
If none of the above methods proved to help fix macro-enabled Excel file corruption, you can try using MS Excel’s built-in Open and Repair utility. This utility helps you repair corrupt Excel files. Proceed with the steps mentioned below to fix a corrupted Excel file –
- Launch MS Excel.
- Click on File.
- Click on Open > Browse.
- Locate & select the problematic Excel file.
- Click on the downward-pointing arrow next to the Open button.
- Click on Open and Repair.
- Excel will show you a dialog box. Click on Repair.
- Once the file is repaired, click Close.
Note – In case of severe file corruption, the Open and Repair functionality may not be able to repair the corrupt Excel file completely. In such a case, you can use the Extract Data option to recover values and formulas from the workbook.
Method 5: Use Excel File Repair Software
The above methods may help you fix macro-enabled Excel file corruption, but sometimes, data restored using these methods might be incomplete or missing some files. To perform an accurate macro-enabled Excel file recovery, you try Stellar Repair for Excel.
It is an advanced Excel file repair software designed to help you repair severely corrupted or damaged macro-enabled Excel files (.xlsm). The software can also repair Excel files of major file formats, including .xls, .xlsx, .xltm, and .xltx and save their content in a new excel file.
It is compatible with all Excel files created using MS Excel 2019, 2016, 2013, 2010, 2007, 2003, or 2000.
Conclusion
As discussed above, a macro-enabled Excel file may get corrupted for multiple reasons, such as malware attacks, bad sectors on a hard drive, or abruptly closing MS Excel, etc. The methods explained above may help you fix corruption and recover some data. However, if you want to recover all types of Excel data, including formulae, calculated values, and graphs along with the data, use Stellar Repair for Excel. The software effortlessly repairs corrupt Excel files and recovers data from them.
