When an Excel file goes haywire, don't give up hope. Using one of these recovery tricks, you may still be able to salvage the data.
Even if you faithfully back up your Excel workbooks, corruption can still be a problem. The backup files won't always contain your most recent work, so you'll probably end up re-entering data. Repairing a corrupted workbook, if possible, is a better option. In this respect, Excel can help. If you attempt to open a corrupted workbook, Excel will engage File Recovery mode, which attempts to repair the workbook. If that works, great! Unfortunately, Excel's automated File Recovery feature sometimes fails to repair a damaged workbook. When this happens, you'll need alternatives. Try the easiest solutions first. The more complex methods usually recover data, but no formulas, formatting, charts, or macros.
1: Let Excel try
If Excel's automatic recovery mode fails, there's a manual feature for recovering a corrupted workbook:
- Choose Open from the File menu. In Excel 2007, click the Office button and select Open.
- Using the Look In control, locate and specify the corrupted workbook.
- From the Open button's dropdown list, shown in Figure A, choose Open And Repair.
- If this is your first attempt to manually recover the workbook, click Repair in the resulting dialog box, shown in Figure B.
Figure A
Attempt to manually recover a corrupted workbook.
Figure B
You can repair a workbook or extract the data from a corrupted workbook.
If you're lucky, Excel will repair the file. However, this feature recovers files when something goes wrong with Excel, so it won't recover every corrupted file. Once you've made every attempt to recovery the file, you might have to make do with recovering just the data. When that's the case, click Extract Data in step 4.
2: If the corruption occurs while the workbook is open
If corruption occurs while the workbook is open, do not save the workbook. You'll just save whatever corrupted the workbook along with the most recent changes. Instead, revert to the last saved version. Doing so, without saving first should discard the corrupted component. You'll possibly lose data, but often, you can rebuild quickly enough (a good reason to save your work often).
To revert to the last saved version, do the following:
- Choose Open from the File menu. In Excel 2007, click the Office button and select Open.
- Using the Look In control, locate and specify the corrupted workbook.
- Click Open.
You're really just reopening the workbook -- there's nothing special going on.
3: Disable automatic calculation
If Excel fails to open the file on its own or via the Open And Repair option, try setting the calculation method to manual and try again. To reset the calculation setting:
- Open a new blank workbook.
- From the Tools menu, choose Options and click the Calculation tab. In Excel 2007, click the Office button, click Excel Options, and select Formulas in the panel to the left.
- In the Calculation section (Calculation Options in Excel 2007), click Manual.
- Click OK.
Then, try to open the corrupted workbook. Sometimes Excel can open a corrupted workbook if it doesn't have to recalculate.
4: Try Microsoft Office Tools
If Excel can't open the corrupted workbook, give Microsoft Office Tools a whirl:
- From the Start menu, choose All Programs (in Windows XP).
- Select Microsoft Office.
- Choose Microsoft Office Tools.
- Select Microsoft Office Application Recovery.
- In the resulting dialog box, shown in Figure C, choose Microsoft Office Excel.
- Click Recover Application. The process could take a few minutes.
- Respond to the Send Report To Microsoft prompt.
Figure C
Office Tools might recover a corrupted workbook.
The recovery tool will close Excel and then restart it, displaying a list of recovered workbooks (if any). If you're lucky, your corrupted workbook will be in the list. Simply open it and count your blessing.
5: Move the file
Sometimes, a corrupted workbook isn't really corrupted in the traditional sense. It just isn't accessible as usual; networks and servers often mask errors as corrupted files. If you encounter a seemingly corrupted workbook that Excel can't repair, move the corrupted file to another folder, drive, or server.
6: Let the competition try
Some people claim extraordinarily good results using OpenOffice Suite, a free open source alternative to Microsoft Office. This suite's Excel counterpart, Calc, should open a corrupted Excel file. Unfortunately, it means a lengthy download, but if it works, it's well worth the effort.
7: Open the corrupted workbook in WordPad or Word
If you can't repair the workbook, try opening it in WordPad. If it works, WordPad will convert everything to text -- but you'll be one step closer to recovering important data. Unfortunately, this method won't recover formulas. On the other hand, unlike many other data recovery methods, WordPad will recover your VBA procedures (macros). Simply search recovered text for Sub and Function to find them.
You might also be able to open the corrupted .xls file in Word, but the results will be limited. For this method to work, you must install the Microsoft Office Excel converter. And unlike WordPad, Word won't recover your procedures.
8: Use external references
Sometimes, you can recover data by referring to the actual cells in a corrupted workbook. You won't recover formulas, formats, charts, macros, and so on, but recovering the data is better than recovering nothing. To recover data by referencing the corrupt workbook, do the following:
- Open a new workbook and enter into cell A1 a formula in the following form to reference A1 in the corrupted workbook: nameofcorruptedworkbook!A1. (You don't need to include .xls in the filename.)
- Press Enter.
- If the corrupted workbook isn't in the same folder, Excel will display the Update Values: nameofcorruptedworkbook dialog box. Use the Look In control to locate the corrupted file. Select the file and click OK.
- If the Select Sheet dialog box appears, as shown in Figure D, select the appropriate sheet and click OK. Excel will display the value in cell A1 of the corrupted workbook.
- Select cell A1 and drag it across as many columns as needed to accommodate the data in the corrupted file. If Excel displays the Update Values:nameofcorruptedworkbook dialog box, select the corrupted file and click OK.
- Repeat step 5, copying row A down as many rows as necessary to accommodate the data in the corrupted file.
- Select the data and choose Copy from the Edit menu.
- Select Paste Special from the Edit menu and choose Values, as shown in Figure E.
- Click OK.
Figure D
If the corrupted file has multiple sheets, specify a sheet.
Figure E
Replace the referencing formulas with data.
9: Try SYLK format to recover data
Microsoft recommends using the SYLK format to filter out corrupted elements, especially if the corruption is printer related. You must be able to open the corrupted workbook for the following to work:
- From the File menu, choose Save As. In Excel 2007, click the Office button.
- From the Save As Type control, choose SYLK(Symbolic Link) (*.slk), as shown inFigure F.
- Give the active sheet a descriptive name, such as corruptedworkbooknameSheet1. If the workbook has only one sheet, this step is unnecessary.
- Click Save.
- If the workbook has multiple sheets, Excel will ask you if you want to continue because the selected format doesn't support multiple sheets. Click OK.
- If Excel prompts you with a warning that the workbook might contain features that aren't compatible with the SYLK format, click Yes.
Figure F
Save each sheet using the SYLK format.
It's important to know that the SYLK format saves only the active sheet. However, you won't notice that the format has stripped all of the pages but the active one until you close and reopen the .slk file. To recover data from all the sheets, you must reopen the corrupted workbook and save each sheet individually. That's why step 3 instructs you to give the sheet a descriptive name. You'll find those names helpful when reconstructing the multiple-sheet workbook.
After saving all the sheets to the SYLK format, open one of the .slk files and rename it using the .xls format. Be careful not to use the corrupted workbook's name. Once you've reconstructed the workbook, you can discard the corrupted file or rename it, freeing up the original name. Don't replace it until you're sure you've recovered as much data as possible. Then, reconstruct the workbook by importing or copying data from the .slk files. It's a tedious process, but worth the effort if nothing else works. This method saves values resulting from formulas, but not the formulas themselves.
10: Recover macros
If you can recover data but not macros via one of the previous tips, you can still save macros -- at least sometimes. To try, do the following:
- Open Excel, but don't open the corrupted workbook.
- Set the calculation mode to Manual (see #3).
- Choose Macro from the Tools menu, select Security, and choose the High option. In Excel 2007, click the Office button, click Excel Options, and choose Trust Center in the left panel. Then, click the Trust Center Settings button, select Macro Settings in the left panel, select Disable All Macros Without Notification in the Macro Settings section, and click OK twice.
- Open the corrupted workbook. If Excel opens the workbook, you'll see a notice that the macros are disabled. If Excel shuts down, this method won't work.
- Press [Alt]+[F11] to open the Visual Basic Editor (VBE).
- Using the Project Explorer (press [Ctrl]+R), right-click a module, and choose Export File.
- Enter a name and folder for the module.
- Repeat steps 6 and 7 as many times as necessary to export all the modules.
- Close the VBE and exit Excel.
- Open a new blank workbook (or the newly constructed workbook that contains recovered data from the corrupted workbook) and import the modules.
Ref:http://www.techrepublic.com/blog/10-things/10-ways-to-recover-a-corrupted-excel-workbook/
