Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    sydney, nsw, australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Workbooks.Open fails when file needs repair

    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:
    " <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
    <logFileName>error037800_02.xml</logFileName>
    <summary>Errors were detected in file 'PATH-AND-FILENAME-DELETED-FOR-POST'</summary>
    - <repairedRecords summary="Following is a list of repairs:">
    <repairedRecord>Repaired Records: Drawing from /xl/drawings/drawing12.xml part (Drawing shape)</repairedRecord>
    </repairedRecords>
    </recoveryLog>"
    Thanks for your help!

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    try
    Code:
    Workbooks.Open(filename:=PATH AND FILENAME,corruptload:=xlrepairfile)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •