[Previous] [Contents]

Extract Data from a Corrupt Workbook

Workbook corruption can mean the loss of vital data, costing you more than just money. This tutorial explores some methods that might recover your data.

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. Lesson 1: always back up your data somewhere. Realistically, though, this does not always happen, and corruption can, of course, occur right before your regularly scheduled backup.

To add to your frustration, even though you know your workbook is corrupt, you sometimes might still be able to open it and even perform certain actions in it.

If You Can Open Your Workbook

If you can open the offending workbook, before doing anything else, be sure to save a copy of it; otherwise, you might regret it. If you have a copy, you can always seek professional help!

Now, try opening the workbook in a later version of Excel and simply saving. Obviously this is not possible if you already are using the latest version of Excel.

If this doesn't work, try opening your workbook and saving the file in HTML or HTM format, then close the file and reopen it, this time saving again in the format you require-e.g., .xls.

When saving in HTML or HTM format, the following features will be lost:

  • Custom views

  • Unused number formats

  • Unused styles

  • Data consolidation settings

  • Scenarios

  • Natural language formulas (they are converted to standard range references)

  • Custom function categories

  • Strikethrough, subscript, and superscript elements

  • Change History

  • Customized page setup settings for charts that are embedded on a worksheet

  • List settings for ListBoxes and ComboBoxes from the Forms toolbar

  • Conditional formatting that is stored on an XLM macro sheet

Also, shared workbooks will no longer be shared. The "Value (Y) axis crosses at category number" setting on the Scale tab of the Format Axis dialog box is not saved if the "Value (Y) axis crosses a maximum category" checkbox is checked. The "Vary colors by point" setting in the Format Data Series dialog box is not saved if the chart contains more than one data series.

Finally, try opening your file and saving it in SYLK (.slk, for symbolic link) format. 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 such as .xls.

If You Cannot Open Your File

If your workbook is corrupt to the point that you cannot even open it, open your spreadsheet in Microsoft Word or via the Spreadsheet viewer, which can be downloaded from the Microsoft web site, then copy your data from the open file. Much of your formatting, formulas, etc., will, however, be lost.

Next, open a new workbook and create an external link to the corrupt workbook-e.g., ='C:\Documents and Settings\Raina\My Documents\[ChookSheet.xls]Sheet1'!A1. Copy this link down as many rows and across as many columns as needed. Do the same for each worksheet in the workbook. 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.

One final thing you can do is visit the OpenOffice.org web site and download the free version of OpenOffice.org. Except for different names for different tools and commands, OpenOffice.org is very similar to Excel. OpenOffice.org is based on the same basic spreadsheet structure as Excel, making it simple for Excel users to use. In fact, about 96% of the formulas used in Excel can be created and applied by using the spreadsheet in OpenOffice.org.

To download the free version of OpenOffice.org, go to http://download.openoffice.org/index.html and download it from the FTP site of your choice. Then install the program. OpenOffice.org is also available for Macs.

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

Sadly, if none of these methods works, you probably will have to pay to try to have your workbook recovered with special software.

After purchase and installation, run the ExcelFix program. Click Select File, select a corrupt file, and then click Diagnose to recover the file. You should now see the recovered file in the workbook viewer. Click Save Workbook to save the workbook into a new readable file that you can open from Excel.

Also available is a demo version that does not enable you to save the file, but all versions of the program enable you to start again and recover as many files as you want.

[Previous] [Contents]