Opening Corrupt Excel files
Sometimes one gets unlucky and a file is corrupted. This may result in the file not opening at all or in error messages during opening.
Here are some options to try and open a corrupt file or retrieve information out of it.
- Set calculation to manual( Open Excel, Choose File, Options from the menu, click the Formulas tab and choose Manual. Hit OK. Now try to open the file.
- Open the file, but disable macros (assuming you've set macro security to medium: File. Options, Trust Center, Trust Center settings, Macros tab)
- As soon as you've clicked the disable macros button, press control-pageup or control-pagedown, thus changing sheets. Sometimes the corruption is inside a chart on a specific sheet, changing the sheet to another may prevent a crash, enabling you to copy the other sheets.
If that does not work, try creating a link to the file in an empty cell in a fresh workbook. Type this formula (adapt path, filename and sheetname):
='c:\my documents\[MyFileName.xls]Sheet1'!A1
and copy right and down. This at may get you the worksheets values.
if you don't know the sheet names, omit it by entering this:
='C:\PathToFile\[MyFileName.xls]'!A1
and press enter. If Excel is able to extract them, it will prompt you for a sheet name.
Sometimes the Excel viewer, Word or even Internet explorer enables you to open the file and copy information out of it.
If the file is not protected, maybe you can open it with OpenOffice (www.Openoffice.org).
Also, Excel can sometimes repair XL files with trouble. Simply choose File, open, select the problem file and instead of simply hitting the "Open" button, click to the right of it on its tiny down arrow to expose more options (see screenshot below)
Finally, sometimes a corrupt file that still can be opened, can be cured by copying all of its worksheets to a new workbook.
Comments
Showing last 8 comments of 148 in total (Show All Comments):Comment by: Jan Karel Pieterse (8-8-2013 21:11:48) deeplink to this comment
Hi Ryane,
In that case I'm afrais your file may have been corrupted beyond retrieval of the data. Right-click the file in windows explorer and select properties. Is there a Prvious versions tab available?
Comment by: forsche@hotmail.com (21-1-2015 22:41:06) deeplink to this comment
Hi Jan
I have this nasty issue with Excel 2010 and I don't know if you have experienced it: I have a macro-enabled workbook with lots of activex controls in sheets and userforms. The original workbook (O) runs on Excel 2010 32 bit and 64 bit and Excel 2013 32 bit. All machines are Windows 7. When I open O with Excel 2010 64 bit and save as O2010-64bit, I cannot run O2010-64bit on Excel 2010 32 bit eventhough I have not changed anything. I get the error 32809 when I open the workbook. 02010-64bit runs fine on Excel 2013 32 bit. It is as if Excel saves the file identifying which Excel it came from. I say this because when I open O on Excel 2010 32 bit and save as 02010-32bit without change, O2010-32bit still runs on Excel 2010 32/64 bit and Excel 2013 32 bit. Any clue to what is going on?
Regards
Emmanuel
Comment by: Jan Karel Pieterse (22-1-2015 07:00:00) deeplink to this comment
Hi Emmanuel,
Are these Office versions on different machines? If so, not all of those machines probably have installed the security update relating to ActiveX controls:
https://support.microsoft.com/kb/3025036/en-us
Comment by: Ravi Krishna P (19-2-2015 12:17:06) deeplink to this comment
Dear Sir
The data (Numerical Numbers) I mentioned in MS Excel sheet contains 50 rows in A and B column. I wanted to club them in single column. For that I converted the sheet in csv and after that I made it to text tab De limited. But when I imported the data from text source to excel. It shows 60 rows. In fact file is not corrupted. But why data is not shown in fifty rows. what is this unnecessary data of 10 rows. Text shows exact data. But if it is imported or pasted in excel sheet it shows more than 50 rows. please give me the solution to reflect exact data in excel columns
Comment by: Jan Karel Pieterse (19-2-2015 13:20:12) deeplink to this comment
Hi Ravi,
Try deleting the rows (delete entire rows!) below your actual data and saving the file.
Comment by: Gurbachan (29-7-2016 12:10:37) deeplink to this comment
Hi Jan,
I had raised my query earlier, but don't know if it reached you. Need help with resolving the error "This workbook cannot be opened or repaired by Microsoft excel because it is corrupt" which I receive after running the macro.
Will share the coding on info@jkp-ads.com
Comment by: Jimmy Johnson (18-4-2019) deeplink to this comment
Yo guys if you're getting this error in Excel, this is the fix:
Locate and then select the following registry subkey:
HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options\
On the Edit menu, point to New, and then click DWORD Value.
Type ForceVBALoadFromSource, and then press Enter.
In the Details pane, right-click ForceVBALoadFromSource, and then click Modify.
In the Value data box, type 1, and then click OK.
Comment by: Jan Karel Pieterse (18-4-2019 00:00:01) deeplink to this comment
Hi Jimmy,
Thanks for the heads up. I searched for this and found this KB article which explains what that registry key does:
https://support.microsoft.com/en-nz/help/4011597/december-5-2017-update-for-excel-2013-kb4011597
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.