We found a problem with some Content

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,285
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
@Jon Peltier - we all feel your pain :eek:

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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top