Unrecoverable Errors with Excel on the increase... Tables at fault?

[Gavona]

I have noticed that I am getting an increased number of corrupt excel files. "Excel found unreadable content in 'xxxx.xlsm' Do you want to recover......" Sometimes Excel can recover most of the info but other times it can't.

It could be coincidence but it seems to me that this is since I started using Excel tables and linking pivot tables to them. Previously I had used pivots and named ranges and my pivots were often 2003 compatible. However recently my organisation moved everyone to 2010 and I stopped trying to maintain compatibility.

Clues? Typically the tables will comprise some columns to the right which I paste data from a tab-delimited (SAP) file into into and some columns in the left with formulae in, including in some cases lookups to external 2003 workbooks. There will be several tables in the workbook. Usually all seems fine while I am working on or save the files. It is only when I try to open a workbook that I get the issue. And the workbooks tend to include VBA.

So can anyone suggest what I can do to minimise the occurrence or to manage it when it does happen.

(With the current workbook I encountered the problem, went back to an earlier version that opened ok and re-made my changes only to encounter the same problem when I try to open it (hopefully I can remember most of what I did last Thursday). Opening with calculation off doesn't help. )

Gavin

 

[xlhelp]

Try copying all the sheets into a new Excel file and see if error persists.


Never miss an opportunity to shut up

 

[zelgar]

I second xlhelp's suggestion of copying everything into a new spreadsheet. There's a chance that your spreadsheet has gotten a corrupt cell(s) that are giving you headaches.

To minimize the chance of copying the corrupted cell into the new spreadsheet:
1. DO NOT just copy the entire sheets into a new sheet.
2. For raw data, Copy & Paste Special (Values)
3. For formulae, the most secure way is actually coping the code from the ribbon bar & pasting into the new sheet cell. Otherwise, just copy & paste as normal.
4. Copy the macros over in VB

Odds are there's a cell or 2 that has gotton corrupt and it appears blank or there's some code in VB that is causeing the trouble.