Workbook opening but with unreadable content

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
245
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~
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I'd suggest you have a look at the named ranges in the before and after versions to see which one is the issue.
 
Upvote 0
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~
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
Then I guess yu pretty much have to assume occasional corruption. Does specifying the Corruptload argument for Workbooks.Open help?
 
Upvote 0
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~
 
Upvote 0
Rather than just using:

Code:
Workbooks.Open filename:="..."

you can add:

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

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

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