Excel Workbooks.Open fails when file needs repair

Topic

New Reply

I am running an Excel VBA macro which loops through each file in a directory, copies a worksheet to another workbook, then closes the original file. This does not work with files which cause an error on opening.

This is a problem as I have a number of files from a third party, each of which causes an error (although the data which I need is unaffected), as described below.

When the macro reaches the command

Code:

Workbooks.Open(PATH AND FILENAME)

it crashes with the error

an error 1004 ‘Method ‘Open’ of object ‘Workbooks’ failed

What changes do I need to make to my code so that Workbooks.Open will open the affected files, ignoring the “unreadable content” error?

Background on error

The error on opening files (which seems to be the subject of many different posts in different forums) is:

“Excel found unreadable content in ‘FILENAME-DELETED-FOR-POST.xlsm’. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.”

On clicking “Yes”, the following message box appears:

[INDENT]
[/INDENT]
[INDENT]”
[/INDENT]
[INDENT]–
[/INDENT]
[INDENT] error037800_02.xml
[/INDENT]
[INDENT]

Errors were detected in file ‘PATH-AND-FILENAME-DELETED-FOR-POST’

[/INDENT]
[INDENT]–
[/INDENT]
[INDENT] Repaired Records: Drawing from /xl/drawings/drawing12.xml part (Drawing shape)
[/INDENT]
[INDENT]
[/INDENT]
[INDENT] ”
[/INDENT]

Thanks for your help!

Reply | Quote

Replies

Hi

Perhaps you could include an On Error section in your code which could log the name of the file that had a problem, skip that particular file, then process all the rest.
Afterwards, perhaps you could review the list of files with problems, then recover and save these manually, then re-process using your VBA routine.

zeddy

Reply | Quote

try

Code:

Workbooks.Open(filename:=PATH AND FILENAME,corruptload:=xlrepairfile)

Reply | Quote