We found a problem with some Content

[kgkev]

I have a work book that has remained unchanged in excel 2013 for years. I have moved to Office 365 and now every time the book opens I get an error and the excel book is repaired.

I then have to save as. Rename the file back to the original name and then reopen it without the error.

However, no matter what I do, the next time I open this work book I get the same error and have to repair, resave & rename.


Is there a log somewhere that shows what is being repaired so I can try and clear the problem manually?
What else could I try?

 

[Yongle]

To prervent risk of recurrence I would simply replace the workbook with a "clean" one
- you have not detailed what the workbook contains and so that may be easier said than actually done!

Assuming the workbook is a relatively simple one ...
1 create new workbook with the same sheet names
2 sheet by sheet ...
ORIGINAL wb select used range & Copy
NEW wb Paste Special paste COLUMN WIDTHS
NEW wb Paste Special paste ALL

 

[kgkev]

Your right, this would normally be my first option that has worked in the past, however this is a fairly large workbook with multiple ODBC links and VBA scripts.
I think recreating it would be too complicated.
Do I have any other options?

 

[Yongle]

Sorry I do not have much to offer here - Excel does not provide much information when files are repaired

And more pertinent possibly is a quote attributed to someone working for Microsoft
“Please keep in mind that it's often quite difficult, if not impossible, to determine where corruption comes from. Corruption can exist in the "shell" of the workbook, or in certain areas, such as a PivotTable, styles, defined names, objects, or the calculation chain/formulas. Corruption can be caused by many different scenarios, for example, a network glitch while saving, a power surge, copying and pasting in corruption from another file, the list goes on. You can compare file corruption to getting a nail in your tire. The nail may be stuck in your tire for a long time without you even noticing, and then all of a sudden your tire goes flat, or the file becomes unreadable or displays strange symptoms.”

In your case the "glitch" became noticeable after upgrading to 365 - which is when your tyre(UK spelling) went flat!
- it is quite likely a minor compatibility issue (but where??)
- you are not alone in experiencing problems with some older files (particularly any created originally on versions prior to 2013)

Good luck

 

[Johnny C]

A good place to chase it down, go into the File menu and 'Check for issues' where it says Inspect Workbook. That's in Excel 2013, it may be elsewhere or worded differently, or maybe gone altogether, I haven't tried 365.
But if it's there it can tell you all sorts of useful things. I had a workbook with links, I needed to remove them. I checked all formulae, Names, charts etc and couldnt find any. I ran that and it told me of data validation referring to links in other documents (where a cell had been copied over) and I was able to clean them all.

 

[Jon Peltier]

This is the most agonizing message in all of Excel. It doesn't tell you where the problem is, or what the problem is. It just refuses to save the file as is, instead it saves a "repaired" file which has lost all of its charts and pivot tables, and formulas have been reduced to =#N/A or #NAME !

A file will self destruct today, then reboot the whole machine and tomorrow it's fine. Or it's fine today, and reboot tomorrow and it explodes. No rhyme or reason, and it never happened like this in Excel 2010. When did Excel workbooks become so fragile?

Sorry for the rant, but it happened today and ate up an hour of a day which was already too busy.

 

[Yongle]

@Jon Peltier - we all feel your pain

And that is precisely why I prefer to create a "clean" workbook immediately
- data (and time) is too precious to risk the problem deteriorating further
- digital errors have a bad habit of compounding
- the corruption may be very minor today but tomorrow Excel may refuse to open the workbook

 

[mole999]

I had a file playing up at work (2013), brought it home to work on and and it opened perfectly in 2019. ran all the usual tests over it, resaved it, took it back to work and no further issues. An to reiterate it is always worth backing up a file occasionally so that changes and development are available from recent history

 

[kgkev]

I have fixed my problem, it took a few hours though


1. Delete all sheets other than the Main page. Saved, reopened - No error. this proved the hardest sheet to recreate was not the problem......Phew!!!
2. Delete each sheet 1 by 1 save and reopen until the error didn't appear.
3. Go back to the original file, delete sheet "Works Orders" save and open....No error.
4. Copy each component back from the damaged book, closing and reopening.

Traced the problem back to an Index Match function.


=INDEX(A4:A36,MATCH(B1,H4:H23,0),1)

should be
=INDEX(A4:A36,MATCH(B1,H4:H36,0),1)

I just can't see how this caused such a crippling error.

 

[kgkev]

I'm unsure of the rules of swearing on the board so will just leave enough blank space so you get the idea...
.
.
.
.
.
Numerous opening and closings of the book without error.
I post the above message, thoroughly proud of myself.

Open the book again to finally get back to the day job. Same error again.

I can only assume there must be a secret link to mrexcel.com/forum in my workbook.

I will try and locate a copy of excel 2019 now