Workbook opening but with unreadable content

[zombiemaster]

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~

 

[RoryA]

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]

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]

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]

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]

Then I guess yu pretty much have to assume occasional corruption. Does specifying the Corruptload argument for Workbooks.Open help?

 

[zombiemaster]

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]

Rather than just using:

Workbooks.Open filename:="..."

you can add:

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

 

[zombiemaster]

Rather than just using:

Workbooks.Open filename:="..."

you can add:

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

Oh, cool! Will that take care of both popup messages or just the first one?
~ZM~