none
VBA - open corrupted file RRS feed

  • Question

  • Hello,

    I have some files that when I manually try to open them come up with the below message:

    Excel found unreadable content in "XXX.xlsx". Do you want to recover the contnents of the workbook? .....

    When I click YES it shows me the below:

    Excel was able to open the file by repairing or removing the unreadable content.

    Removed Feature: Data validation from /xl/worksheets/sheet1.xml part

    And then I can manually save it, replace it, and the file thereafter opens normally with no errors.

    Now my problem is... I want to do the same with a VBA code... 

    I prepared the below and there is no luck... It comes with 1004 error:

    Sub letshope()
    
    With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
            .EnableEvents = False
            .AskToUpdateLinks = False
        End With
    
    Set wb = Workbooks.Open(Filename:= _
    "\\folder\XXX.xlsx", _
    UpdateLinks:=0, Password:="password", _
    IgnoreReadOnlyRecommended:=True, _
    CorruptLoad:=XlCorruptLoad.xlRepairFile)
    
    
    
    End Sub
    
    

    Unfortunately everybody on the web suggests that with the "CorruptLoad:=XlCorruptLoad.xlRepairFile" part all corrupted files open...

    This is not the case... 

    Anyone who might have any kind of idea please?

    It is a painful process opening them manually, saving them etc....

    Thanks!!!

    Friday, February 23, 2018 12:00 PM

All replies

  • Hi,

    I checked if the issue was reproduced.
    If variabel "wb" is correctly defined, what you need to do is to use mapped drive letter.
    "\\folder\XXX.xls" cannot be used. Please describe like this (using mapped drive letter):
       "R:\00 temp\test_01.xlsx"
       ("R" is a mapped drive)

    Regards,

    Ashidacchi

    Friday, February 23, 2018 10:24 PM
  • Hell Yan-A,

    I tried to faulty edit a workbook with OpenXML and use the workbook as corrupted file.

    I could reproduce your issue with the file, using VBA code would fail to open corrupted file. 

    As workaround, I would suggest you manually open the file and use the repaired file to replace original file in the folder.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, February 26, 2018 7:46 AM
  • Hi,

    I checked if the issue was reproduced.
    If variabel "wb" is correctly defined, what you need to do is to use mapped drive letter.
    "\\folder\XXX.xls" cannot be used. Please describe like this (using mapped drive letter):
       "R:\00 temp\test_01.xlsx"
       ("R" is a mapped drive)

    Regards,

    Ashidacchi

    Hi Ashidacchi, 

    thanks for your input. I changed the path and I added the mapped folder but again it didnt open.

    My path looks something like this:

    "T:\ZZZ1\ZZZ2\ZZZ3\ZZZ4\ZZZ5\XXXXXX.xlsx"

    it came up with the same error.... Unfortunately it doesnt give me any further hint. 

    Only when I manually open the file and press to repair it I am getting the below:

    Removed Feature: Data validation from /xl/worksheets/sheet1.xml par

    and then:

    error067760_07.xml

    <?xml version="1.0" encoding="UTF-8" standalone="true"?>
    -<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error067760_07.xml</logFileName><summary>Errors were detected in file 'XXXXXXX.xlsx'</summary>-

    <removedFeatures summary="Following is a list of removed features:"><removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet1.xml part</removedFeature></removedFeatures></recoveryLog>

    any ideas please???

    Monday, February 26, 2018 9:47 AM
  • Hell Yan-A,

    I tried to faulty edit a workbook with OpenXML and use the workbook as corrupted file.

    I could reproduce your issue with the file, using VBA code would fail to open corrupted file. 

    As workaround, I would suggest you manually open the file and use the repaired file to replace original file in the folder.

    Best Regards,

    Terry


    Hi Terry,

    Thanks for this. So you reckon there is no workaround to open them with a vba script? They are not a few.... on a daily basis they are plenty and heavy files.... so I would prefer to automate the task.

    Thanks!

    Monday, February 26, 2018 9:49 AM
  • Hello Yan-A,

    It is hard to say and it should depend on the corrupted reason. According to my test result, I think it is not possible to open the workbook with invalid xml part in VBA script.

    I would still suggest you fix corrupt source file manually instead of opening it with error every time.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 27, 2018 6:29 AM
  • Hi Yan-A,

    Does your file 'XXXXXXX.xlsx' have [Data Validation] in some cells?
    In many cases, [Data Validation] is used for drop-down list to choose an item from it.
    Regards,

    Ashidacchi

    Tuesday, February 27, 2018 7:24 AM
  • Thanks both of you! 

    I followed your advice and i finally came up with the reason of the corrupted files.

    Some users were copying and pasting data from one file to another that contained data validation. So in the end I do not have any corrupted files again. Still no sollution how to open the corrupted ones but happy end afterall.

    Monday, March 5, 2018 9:26 AM