Unreadable Content error

I get an error message saying Excel found unreadable content, would you like to recover...... when i open my workbook. I click OK and all is well, and the workbook opens as normal. Just would like to get to the root of the problem to figure out what is causing this error.

After some searching around some have listed that Named Ranges could cause this. My workbook has 100s of rows with named ranges in the formula. Most of the workbook contains sumproduct formulas, and vlookups. Any other ideas or ways to find out where the error is? There are no vba functions or macros in this workbook.

I encountered the Unreadable Content/Named Ranges problem but there were no Named Ranges in the Formulas/Name Manager. I had no way to find the root cause.

I found on the web-lore that Excel 2007 adds hidden named ranges.

These named ranges can be unhid in VBA and then deleted in the Names Manager. When I deleted them from my Xlsm template, the template no longer produced the Unreadable Content error.

Here is the exciting VBA macro...

Please Login or Register  to view this content.

Sorry I lost the originator of this code. She/He is like unto the very gods of the good and great.

I was using the XLSM template in MS Access to build a reporting workbook, adding tabs from queries by Transferspreadsheet. The users were modifying the template each month by pasting data from another workbook, which somehow brought in the evil hidden named ranges.

I also found that letting Excel remove the content, as per the opening msgbox, the template could be reconstructed from the cleaned up Xlsm. Thus I had 2 workarounds.

Don't know if the problem will re-occur when the users add new material to the template but at least we have a fix.