Results 1 to 7 of 7

Thread: [RESOLVED] Cannot Open more than 1 Corrupt File in a Module

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    6

    Resolved [RESOLVED] Cannot Open more than 1 Corrupt File in a Module

    I am trying to work through 2 very large (about 15 mb. each) reports that are automatically generated on a daily basis. Both files are corrupt, but can be repaired when manually opened, then clicking the "Yes" button when it asks if you want to repair it. I cannot fix the files as they belong to a different department.

    The VBA code for both reports (workstation and server) is identical, only the filepaths and filenames are different. When I run the code, the first code (workstation) runs fine, but when I get to the second part of the code (server), I cannot open the server file. The code errors on this line:

    ' Open today's server Review report
    Workbooks.Open Filename:= _
    "Z:\Server\Server_Review_" & Format(Now(), "YYYYMMDD") & ".xlsx"

    I receive the following error:

    Run-time error '1004':
    Method 'Open' of object "workbooks' failed

    It looks like the code never gets to the error handler part. I tried switching the code so that the server code is before the workstation code. Oddly, the server code (now the first code to be processed) opened just fine, but the now lower workstation code failed to open the file. The code now errors on this line:

    ' Open today's workstation Review report
    Workbooks.Open Filename:= _
    "Z:\Workstation\Workstation_Review_" & Format(Now(), "YYYYMMDD") & ".xlsx"


    Is there some sort of problem within Excel or the VBE where it cannot open 2 corrupt (but repairable) files in a single module? Here's the code:

    **************************************************************************************************** ***************************************************


    Sub Workstation_andServer_Code()

    ' **************************************************************************************************** *******
    ' **************************************************************************************************** *******
    ' Workstation Review Code
    ' **************************************************************************************************** *******
    ' **************************************************************************************************** *******

    ' Opening the file may cause a problem if it is corrupt
    On Error GoTo Workstation_ErrorHandler

    ' Open today's workstation Review report
    Workbooks.Open Filename:= _
    "Z:\Workstation\Workstation_Review_" & Format(Now(), "YYYYMMDD") & ".xlsx"

    ' Open and repair the file if necessary
    Workstation_ErrorHandler:
    If Err.Number = 1004 Then
    Workbooks.Open Filename:= _
    "Z:\Workstation\Workstation_Review_" & Format(Now(), "YYYYMMDD") & ".xlsx", _
    CorruptLoad:=xlRepairFile
    End If

    ' Close the file
    ActiveWorkbook.Close savechanges:=False



    ' **************************************************************************************************** *******
    ' **************************************************************************************************** *******
    ' Server Review Code
    ' **************************************************************************************************** *******
    ' **************************************************************************************************** *******

    ' Opening the file may cause a problem if it is corrupt
    On Error GoTo Server_ErrorHandler

    ' Open today's server Review report
    Workbooks.Open Filename:= _
    "Z:\Server\Server_Review_" & Format(Now(), "YYYYMMDD") & ".xlsx"

    ' Open and repair the file if necessary
    Server_ErrorHandler:
    If Err.Number = 1004 Then
    Workbooks.Open Filename:= _
    "Z:\Server\Server_Review_" & Format(Now(), "YYYYMMDD") & ".xlsx", _
    CorruptLoad:=xlRepairFile
    End If

    ' Close the file
    ActiveWorkbook.Close savechanges:=False

    End Sub
    *******************************************************************************

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Cannot Open more than 1 Corrupt File in a Module

    if the first file generates an error, you should clear the error, as currently the error will still be set when it gets to the second file

    there are various things you could try, including opening the second (or both) file in a separate instance(s) of excel, still within the same procedure
    you could try working with workbook objects, instead of relying on activeworkbook
    can you open the same file a 2nd time?
    would it still be corrupt, or is the repair permanent without saving?

    this post should probably be in the office development forum, a moderator will probably move it for you
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Cannot Open more than 1 Corrupt File in a Module

    Thread moved from the 'VB6' forum to the 'Office Development/VBA' forum (while VBA and VB6 have some similarities, they are not the same thing)

  4. #4
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Cannot Open more than 1 Corrupt File in a Module

    Do the files have vba in too ? could it be that the coding is the same? and it is getting a naming conflict or something (as one can be oipened a

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  5. #5

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    6

    Re: Cannot Open more than 1 Corrupt File in a Module

    Westconn1,

    Thank you for your thoughtful response. I have done some testing using your feedback as guidance. Here's what I found.

    if the first file generates an error, you should clear the error, as currently the error will still be set when it gets to the second file

    The first file generates a 1004 error, but no pop up error box, and I think that is because I have it handled. The error message was cleared as follows, resulting in the same 1004 error box when attempting to open the second file:

    MsgBox (Err.Number)
    Err.Clear
    MsgBox (Err.Number)
    ********************************************

    there are various things you could try, including opening the second (or both) file in a separate instance(s) of excel, still within the same procedure:
    I am still very new to VBA (I am still referring to the Excel VBA Programming for Dummies book). I am not sure what you mean, could you please provide details on how to accomplish this?

    you could try working with workbook objects, instead of relying on activeworkbook:
    Modified the code below, no change

    ' Close the file
    ' ActiveWorkbook.Close savechanges:=False
    Workbooks("Workstation_Review_" & Format(Now(), "YYYYMMDD") & ".xlsx").Close SaveChanges:=False
    **************************************************

    can you open the same file a 2nd time? No, a Run-time error message for 1004 pops up when the code attempts to open the same file a second time, which should not pop up because I supposedly should have it handled? It never gets to the errorhandler part.
    would it still be corrupt, or is the repair permanent without saving? It is still corrupt.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Cannot Open more than 1 Corrupt File in a Module

    I am not sure what you mean, could you please provide details on how to accomplish this?
    try like

    Code:
    set xlapp = createobject("Excel.application")
    on error resume next    ' start inline error handling
    ' Open today's workstation Review report
    set obwb =Workbooks.Open(Filename:= _
    "Z:\Workstation\Workstation_Review_" & Format(Now(), "YYYYMMDD") & ".xlsx")
    
    ' Open and repair the file if necessary
    
    If Err.Number = 1004 Then
       err.clear
       set obwb =Workbooks.Open(Filename:= _
       "Z:\Workstation\Workstation_Review_" & Format(Now(), "YYYYMMDD") & ".xlsx", _
       CorruptLoad:=xlRepairFile)
    End If
    if not err.number = 0 then msgbox "Some other or additional error occurred, " & err.description
    on error goto 0     ' cease in line error handling
    ' Close the file
    obwb.Close savechanges:=False
    set obwb = nothing
    xlapp.quit 
    set xlapp = nothing
    you can try this for either or both workbooks, possibly use different object variables for each workbook, though it would not normally be necessary, any errors outside the inline error handling need to be handled separately

    please note, i just typed the code within the browser, and pasted some of your original code, so it is not tested and may contain typos or code errors
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    6

    Re: Cannot Open more than 1 Corrupt File in a Module

    westconn1,

    I used the code that you provided. It worked wonderfully! Then I looked harder at the error handling that you provided. I reworked all of my code using your error handling, and my problem is now resolved. There was no need to work with the items as objects in this particular case. Thank you for your insightful support.

Posting Permissions

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



Click Here to Expand Forum to Full Width