-
Sep 15th, 2017, 01:02 PM
#1
Thread Starter
New Member
[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
*******************************************************************************
-
Sep 15th, 2017, 05:33 PM
#2
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
-
Sep 16th, 2017, 06:13 AM
#3
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)
-
Sep 18th, 2017, 09:14 AM
#4
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
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...
-
Sep 18th, 2017, 01:38 PM
#5
Thread Starter
New Member
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.
-
Sep 18th, 2017, 04:22 PM
#6
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
-
Sep 21st, 2017, 01:09 PM
#7
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|