Categories
Excel

Repair (or Extract Data from) a Corrupt Workbook

Workbooks sometimes become corrupt for no apparent reason. This can cause all sorts of problems, especially if the workbook is vital and for whatever reason you have no backup. Fortunately, there are some ways to try to repair or extract data from a corrupt workbook.

If you have a workbook that is corrupted and cannot be opened normally, you can try to extract the data from it using one of the following methods. Before doing anything, be sure to save a copy of it; otherwise, you might regret it.

Open and Repair

Try to open the workbook in Excel and use the Open and Repair option. This option attempts to recover as much of the workbook data as possible.

To use this option, go to File > Open > Browse and select the corrupt workbook in the Open dialog box.

  1. Then click the arrow next to the Open button
  2. Choose Open and Repair from the drop-down.

You will see a prompt that asks you to choose between Repair and Extract Data.

  • Choose Repair first and see if it works.
  • Choose Extract Data if the Repair option not works. The Extract Data will extract the values and formulas from the workbook without formatting or charts.

Open Excel in Safe Mode

Sometimes, Excel might be able to repair the corrupted workbook automatically when opened in Safe Mode.

To open Excel in Safe Mode:

  1. Close Excel application
  2. Hold down the Ctrl key and click on the Excel icon.

Now try to open the corrupt workbook. If the workbook opens without any issues, use Save As... to save it with a different name.

Save As Workbook in SYLK Format

Try to save the workbook in SYLK (.slk, for symbolic link) format. This format can help filter out the corrupted elements in the workbook. To do this:

  1. Choose File > Save As > Browse
  2. Choose SYLK (*.slk) from the Save as type.
  3. Then close and reopen the workbook in Excel and save it again as an Excel file.

Note that when you save a workbook in this format, only the active worksheet is saved. So, you will have to do the same for each worksheet. Reopen the file and save it in a desired format.

Use External References

Try to use external references to link to the corrupt workbook. This method can help you retrieve only data and not formulas or calculated values from the workbook. To do this:

  1. Open a new blank workbook in Excel
  2. Create an external link to the corrupt workbook.
  3. Type = followed by the file name of the corrupt workbook in cell A1.
    For example, if your corrupt workbook is named Book1.xlsx, type following formula in cell A1 of the new workbook.
=[Book1.xlsm]Sheet1!A1
'Or type full path
='C:\Users\brainbell\Documents\[Book1.xlsm]Sheet1'!A1
  1. Then press Enter and drag the cell to copy the formula to other cells. You should see the data from the corrupt workbook appear in the new workbook.
  2. Copy this link down as many rows and across as many columns as needed.
  3. Do the same for each worksheet in the workbook.

Note: If you cannot remember any of the names of the worksheets, create any old sheet name using the correct filename path, and Excel will display the sheet names for you when you press Enter.

Unzip The Corrupt Excel File To Extract Its Contents

Change the file extension of the corrupted workbook to .zip and extract the contents. This method works only for workbooks saved in the .xlsx or .xlsm format, which are actually compressed XML files.

Open the zip file, you will see several folders and files inside the zip file. The folder named xl contains the worksheets, charts, and other objects in the workbook.

Extracting a Corrupt Workbook File

You can try to open these files with a text editor or an XML viewer and copy the data from them.

Note: Follow these steps, if you don’t see the extension when you view the workbook in File Explorer:

  1. Open your Documents folder
  2. In File Explorer under View, in the Show/hide group, select the File name extensions check box.
  3. Now rename your workbook’s extension to zip.

Try MS-Office Alternatives

One final thing you can do is visit the OpenOffice.org web site and download the free version of OpenOffice, or visit libreoffice.org and download the LibreOffice.

In many cases, your Excel data can be recovered. However, no VBA code can be recovered due to incompatibility between OpenOffice/LibreOffice and Excel.

Understanding Workbooks and Worksheets: