Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Corrupt Files
Deze pagina in het Nederlands

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.

  1. Set calculation to manual( Open Excel, Choose Tools, Options from the menu, click the Calculation tab and choose Manual. Hit OK. Now try to open the file.
  2. Open the file, but disable macros (assuming you've set macro security to medium: Tools, Macro, security)
  3. 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 XL (2000) is able to extract them, it will prompt you for a sheet name.

Sometimes the Excel viewer, Word or even Internet explorer (v5) 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 XP and 2003 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 using this method (Excel 2000 or up):

  1. Open the file
  2. Save-as filetype Webpage
  3. Close the file
  4. Open the html file
  5. Save-as normal Excel workbook.
 


Comments

Showing last 8 comments of 146 in total (Show All Comments):

 


Comment by: Trevor (3/8/2013 10:42:12 PM)

Tks Jan Karel, it worked for me too, will save me a lot of time in the future, Trevor

 


Comment by: Ryane (7/19/2013 9:57:11 PM)

Hi, I tried your advice but it didn't work for me. I have an excel file that I have been using for years. All of sudden when I open it, I am getting an error message that reads: "File Error" Data may be lost". Then all the cells that contained text are now filled with "#VALUE!". How do I get the text back??

I want to add, it is not doing this on other excel files.

Thanks so much for your help.

 


Comment by: Jan Karel Pieterse (8/8/2013 9:11:48 PM)

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 (1/21/2015 10:41:06 PM)

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 (1/22/2015 7:00:00 AM)

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 (2/19/2015 12:17:06 PM)

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 (2/19/2015 1:20:12 PM)

Hi Ravi,

Try deleting the rows (delete entire rows!) below your actual data and saving the file.

 


Comment by: Gurbachan (7/29/2016 12:10:37 PM)

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

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].