How do you fix Microsoft Excel was attempting to open and repair the file?

Account InformationTechRepublic close modalShare with Your Friends10 ways to recover a corrupted Excel workbook Your email has been sent{{#message}}{{{message}}}{{/message}}{{

How do you fix Microsoft Excel was attempting to open and repair the file?

Account InformationTechRepublic close modal

Share with Your Friends

10 ways to recover a corrupted Excel workbook

Your email has been sent

{{#message}}{{{message}}}{{/message}}{{^message}}Your submission failed. The server responded with {{status_text}} (code {{status_code}}). Please contact the developer of this form processor to improve this message. Learn More{{/message}}

{{#message}}{{{message}}}{{/message}}{{^message}}It appears your submission was successful. Even though the server responded OK, it is possible the submission was not processed. Please contact the developer of this form processor to improve this message. Learn More{{/message}}

Submitting

Image of Susan Harkins

by Susan Harkins in Software on September 8, 2009, 3:19 AM PDT

10 ways to recover a corrupted Excel workbook

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.

When an Excel file goes haywire, dont 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 wont always contain your most recent work, so youll 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, Excels automated File Recovery feature sometimes fails to repair a damaged workbook. When this happens, youll need alternatives. Try the easiest solutions first. The more complex methods usually recover data, but no formulas, formatting, charts, or macros.

Note: This article is also available as a PDF download.

1: Let Excel try

If Excels automatic recovery mode fails, theres a manual feature for recovering a corrupted workbook:

  1. Choose Open from the File menu. In Excel 2007, click the Office button and select Open.
  2. Using the Look In control, locate and specify the corrupted workbook.
  3. From the Open buttons dropdown list, shown in Figure A, choose Open And Repair.
  4. If this is your first attempt to manually recover the workbook, click Repair in the resulting dialog box, shown in Figure B.

Figure AAttempt to manually recover a corrupted workbook.

Figure BYou can repair a workbook or extract the data from a corrupted workbook.

If youre lucky, Excel will repair the file. However, this feature recovers files when something goes wrong with Excel, so it wont recover every corrupted file. Once youve made every attempt to recovery the file, you might have to make do with recovering just the data. When thats 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. Youll 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. Youll 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:

  1. Choose Open from the File menu. In Excel 2007, click the Office button and select Open.
  2. Using the Look In control, locate and specify the corrupted workbook.
  3. Click Open.

Youre really just reopening the workbook  theres 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:

  1. Open a new blank workbook.
  2. 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.
  3. In the Calculation section (Calculation Options in Excel 2007), click Manual.
  4. Click OK.

Then, try to open the corrupted workbook. Sometimes Excel can open a corrupted workbook if it doesnt have to recalculate.

4: Try Microsoft Office Tools

If Excel cant open the corrupted workbook, give Microsoft Office Tools a whirl:

  1. From the Start menu, choose All Programs (in Windows XP).
  2. Select Microsoft Office.
  3. Choose Microsoft Office Tools.
  4. Select Microsoft Office Application Recovery.
  5. In the resulting dialog box, shown in Figure C, choose Microsoft Office Excel.
  6. Click Recover Application. The process could take a few minutes.
  7. Respond to the Send Report To Microsoft prompt.

Figure COffice 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 youre lucky, your corrupted workbook will be in the list. Simply open it and count your blessing.

5: Move the file

Sometimes, a corrupted workbook isnt really corrupted in the traditional sense. It just isnt accessible as usual; networks and servers often mask errors as corrupted files. If you encounter a seemingly corrupted workbook that Excel cant 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 suites Excel counterpart, Calc, should open a corrupted Excel file. Unfortunately, it means a lengthy download, but if it works, its well worth the effort.

7: Open the corrupted workbook in WordPad or Word

If you cant repair the workbook, try opening it in WordPad. If it works, WordPad will convert everything to text  but youll be one step closer to recovering important data. Unfortunately, this method wont 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 wont recover your procedures.

8: Use external references

Sometimes, you can recover data by referring to the actual cells in a corrupted workbook. You wont 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:

  1. 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 dont need to include .xls in the filename.)
  2. Press Enter.
  3. If the corrupted workbook isnt 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.
  4. 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.
  5. 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.
  6. Repeat step 5, copying row A down as many rows as necessary to accommodate the data in the corrupted file.
  7. Select the data and choose Copy from the Edit menu.
  8. Select Paste Special from the Edit menu and choose Values, as shown in Figure E.
  9. Click OK.

Figure DIf the corrupted file has multiple sheets, specify a sheet.

Figure EReplace 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:

  1. From the File menu, choose Save As. In Excel 2007, click the Office button.
  2. From the Save As Type control, choose SYLK(Symbolic Link) (*.slk), as shown in Figure F.
  3. Give the active sheet a descriptive name, such as corruptedworkbooknameSheet1. If the workbook has only one sheet, this step is unnecessary.
  4. Click Save.
  5. If the workbook has multiple sheets, Excel will ask you if you want to continue because the selected format doesnt support multiple sheets. Click OK.
  6. If Excel prompts you with a warning that the workbook might contain features that arent compatible with the SYLK format, click Yes.

Figure FSave each sheet using the SYLK format.

Its important to know that the SYLK format saves only the active sheet. However, you wont 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. Thats why step 3 instructs you to give the sheet a descriptive name. Youll 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 workbooks name. Once youve reconstructed the workbook, you can discard the corrupted file or rename it, freeing up the original name. Dont replace it until youre sure youve recovered as much data as possible. Then, reconstruct the workbook by importing or copying data from the .slk files. Its 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:

  1. Open Excel, but dont open the corrupted workbook.
  2. Set the calculation mode to Manual (see #3).
  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.
  4. Open the corrupted workbook. If Excel opens the workbook, youll see a notice that the macros are disabled. If Excel shuts down, this method wont work.
  5. Press [Alt]+[F11] to open the Visual Basic Editor (VBE).
  6. Using the Project Explorer (press [Ctrl]+R), right-click a module, and choose Export File.
  7. Enter a name and folder for the module.
  8. Repeat steps 6 and 7 as many times as necessary to export all the modules.
  9. Close the VBE and exit Excel.
  10. Open a new blank workbook (or the newly constructed workbook that contains recovered data from the corrupted workbook) and import the modules.

Corrupted Word doc?

If you run into problems with a Word document, check out these recovery strategies.Susan HarkinsPublished: September 8, 2009, 3:19 AM PDT Modified: September 8, 2009, 3:19 AM PDT  See more Software

Video liên quan