Live Chat Software by Kayako |
Knowledgebase: Microsoft Excel
|
Recover Corrupted Excel File
Posted by Wilson Chua on 07 April 2006 04:10 AM
|
|
|
Here is an excellent article on the subject of opening or repairing a corrupted excel file: http://office.microsoft.com/en-ca/assistance/ha010346561033.aspx I am reproducing it below for convenience: Microsoft Excel provides automatic recovery for a corrupted file by attempting to reopen and simultaneously repair the file. Excel identifies what it changed while repairing the file. If the repair fails, Excel again tries to open the file but instead of attempting a repair, Excel extracts cell values and formulas leaving only the data. Under some circumstances, however, Excel won't automatically go into recovery mode, so users may find it necessary to recover file data manually. To manually repair a file On the File menu, click Open. In the Open dialog box, select the file you want to open, and click the arrow next to the Open button. Click Open and Repair, and then choose which method you want to use to recover your workbook. Methods for recovering data from damaged workbooks The following are additional methods you can use to recover data manually from a file that has been corrupted. Under some circumstances a disk error or network error may make it impossible to open a file. In those situations you should move your file to a different hard disk drive or from the network to a local disk before spending time implementing the recovery options outlined here. The following methods are opportunities to save data that might otherwise be lost, so if one method is not successful, try another. You can also try third-party software solutions to recover file data if you can't recover your data using these methods. If you can open the file in Excel Use the Revert To Saved Document command If you are editing a Microsoft Excel worksheet and the file becomes corrupted before you have saved changes to the file, you can recover the original worksheet by doing the following: On the File menu, click Open and select the name of the file that you are editing. A dialog box appears with the message "Revert to Saved Document?" Click OK. The file you are editing reverts to the last saved version of the file. Save the file in SYLK format Saving the file in SYLK format is typically used to remove printer corruption. If you can open the corrupted Microsoft Excel file, you can "filter" it if you save it in SYLK format, close the file, and then reopen it as follows: On the File menu, click Save As. In the Save as type list, click SYLK (Symbolic Link), and then click Save. Note Only the active sheet in the workbook is saved when you use the SYLK file format. Click OK when the message tells you the selected file type does not support workbooks. Click Yes when the message tells you the file may contain features that aren't compatible with the SYLK format. On the File menu, click Close. On the File menu, click Open. Select the .slk file you saved, and click Open. Note To see the .slk file, you may need to click All Files in the Files of type list. On the File menu, click Save As. In the Save as type box, click Microsoft Excel Workbook, and then click Save. Note Because this format saves only the active worksheet, you must open the corrupted file repeatedly and save each worksheet separately. Save the file in Hypertext Markup Language (HTML) format If you can open the corrupted Microsoft Excel file, you can "filter" it if you save it in HTML format, close the file, and then reopen it as follows: On the File menu, click Save As. In the Save as type list, click Web Page (*.htm, *.html). Under Save, click Entire Workbook, and then click Save. Close the file. Open the file again in Excel. On the File menu, click Save As. In the Save as type list, click Microsoft Excel Workbook. Change the name of the file to create a new "filtered" copy without replacing the original. Note Some features may be lost when you save in HTML format. If you can't open the file in Excel Set the recalculation option in Excel to manual Start Excel and open a blank workbook if one is not open on the screen. On the Tools menu, click Options. Click the Calculation tab. In the Calculation section, click Manual, and then click OK. On the File menu, click Open and try to open the file. Use external references to link to the corrupted file This method retrieves only data and not formulas or values from the workbook. On the File menu, click Open. Then, browse from the current folder to the folder that contains the damaged file, and click Cancel. Click New on the File menu, click File, and then click OK. Type =File Name!A1 in cell A1 of the new workbook, where File Name is the name of the damaged workbook. If the Select Sheet dialog box appears, select the appropriate sheet, and click OK. Select cell A1, and on the Edit menu, click Edit. Then, select an area that is approximately the same size as the range of cells that contain data in the damaged file, and then click Paste on the Edit menu. With the range of cells still selected, click Copy on the Edit menu. On the Edit menu, click Paste Special, select Values, and click OK. This step removes the links to the damaged file and leaves only the data. Open the file in Microsoft Word or WordPad If you have the Microsoft Excel converter installed, you might be able to open your Excel workbook in Microsoft Word. If the file does open in Word, you will not be able to recover module sheets, dialog sheets, chart sheets, macro sheets, or any embedded charts. Also, you will not recover any cell formulas, only the results of those formulas that are currently in the cells. You can also open your Excel workbook in WordPad. If the file does open, you might recover Microsoft Visual Basic code in your modules and class modules. Search for the words "Sub" or "Function" to find your code. Open the file in Microsoft Excel Viewer If you have Microsoft Excel Viewer installed, you may be able to open the Microsoft Excel workbook in Microsoft Excel Viewer, copy the cells, and paste the cells into a new workbook. However, you cannot recover module sheets, dialog sheets, chart sheets, or macro sheets. Also, you will not recover any cell formulas, only the results of those formulas that are currently in the cells. To download Excel Viewer 2003, or for more information, see Downloads on Microsoft Office Online. If a chart is linked to the corrupted file, use a macro to extract the data Enter the following macro code in a module sheet: Sub GetChartValues97() Dim NumberOfRows As Integer Dim X As Object Counter = 2 ' Calculate the number of rows of data. NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values) Worksheets("ChartData").Cells(1, 1) = "X Values" ' Write x-axis values to worksheet. With Worksheets("ChartData") .Range(.Cells(2, 1), _ .Cells(NumberOfRows + 1, 1)) = _ Application.Transpose(ActiveChart.SeriesCollection(1).XValues) End With ' Loop through all series in the chart and write their values to ' the worksheet. For Each X In ActiveChart.SeriesCollection Worksheets("ChartData").Cells(1, Counter) = X.Name With Worksheets("ChartData") .Range(.Cells(2, Counter), _ .Cells(NumberOfRows + 1, Counter)) = _ Application.Transpose(X.Values) End With Counter = Counter + 1 Next End Sub Insert a new worksheet into your workbook and rename it ChartData. Select the chart from which you want to extract the underlying data values. Note The chart can either be embedded on a worksheet or on a separate chart sheet. Run the GetChartValues97 macro. The data from the chart will be placed in the ChartData worksheet. Saving a backup copy of your file As a preventative measure, you may want to save your file often and create a backup copy every time you save. In the Save As dialog box (File menu, Save As command), click Tools, click General Options, and then select the Always create backup check box. This way, you'll have access to a good copy of the file, should the original be accidentally deleted or become corrupted. You can also make sure that Excel automatically creates a recovery file at specific intervals. In the Options dialog box (Tools menu, Options command), on the Save tab, select the Save AutoRecover info every check box, and then specify a number of minutes. In the AutoRecover save location box, specify the location where you want to save the recovery file. Make sure that the Disable AutoRecover check box (under Workbook options) is not selected. | |
 (149 vote(s)) This article was helpful This article was not helpful |
|
Comments (0)