Workbook opening but with unreadable content

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
196
I have a macro that opens a shared workbook, renames it and saves it again in a different folder, then creates the file again in the original location for that day's work. This macro is run by different team members each day, and none of them are tech-savvy.

This has been running very well for a number of years, but once in a while the file will become corrupted and will not open without clicking YES on a popup box that says this:

"We found a problem with some content in "FILE NAME". Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

I click yes and it opens but I get a message:

"Excel was able to open the file by repairing or removing the unreadable content.
Removed records: Named range from /xl/workbook.xml part (Workbook)"

These two popups are stopping the VBA dead and generating a run-time error, so the user stops and contacts me for help. I go into the folder, move the file myself, create a "dummy file" for the macro to see and tell the user to start over and it goes on it's merry way with the rest of the code as usual.

So - my question is two-fold.

1) is there code that can be used to automatically click "YES" on the first popup, then click "OK" on the second one so the file can then be renamed and re-saved to the next location?

2) is there a way to find out what the 'unreadable content' is, so we can avoid it from happening at all? When I click on the link that the second popup provides for the error log, all that is there is the exact same message that is on the popup about "Removed records: Named range from..."

Thanks for any help!
~ZM~
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I'd suggest you have a look at the named ranges in the before and after versions to see which one is the issue.
 

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
196
I'd suggest you have a look at the named ranges in the before and after versions to see which one is the issue.
Thanks for the thought, but I should have mentioned (sorry!) that the only named ranges in the workbook are:

Print_Area
Print_Titles

And those are system-generated, I believe, because I am the one who created this report and I hardly ever used named ranges during the time frame I created this...

~ZM~
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
When you say a shared workbook, so you mean an actual Shared Workbook (shudder) or just a workbook that more than one person has access to?
 

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
196
When you say a shared workbook, so you mean an actual Shared Workbook (shudder) or just a workbook that more than one person has access to?
Yes, that shudder is warranted - management wanted a Shared Workbook for this report, so that's what it is. Multiple people can access it at the same time and save changes (mostly color-formatting).
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Then I guess yu pretty much have to assume occasional corruption. Does specifying the Corruptload argument for Workbooks.Open help?
 

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
196
Then I guess yu pretty much have to assume occasional corruption. Does specifying the Corruptload argument for Workbooks.Open help?
Sorry, I'm only in intermediate user, and don't know what that means...? Can you explain a bit? If not I can do a web search.
~ZM~
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Rather than just using:

Code:
Workbooks.Open filename:="..."
you can add:

Code:
Workbooks.Open filename:="...", corruptload:=xlRepairFile
 

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
196
Rather than just using:

Code:
Workbooks.Open filename:="..."
you can add:

Code:
Workbooks.Open filename:="...", corruptload:=xlRepairFile
Oh, cool! Will that take care of both popup messages or just the first one?
~ZM~
 

Watch MrExcel Video

Forum statistics

Threads
1,099,083
Messages
5,466,538
Members
406,485
Latest member
kaksolver

This Week's Hot Topics

Top