Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to catch error 1004, don't work in 2nd loop...
Hi there...
ThisWorkbook.Sheets represents the workbook that has all the sheets that will be used for the export to different folders. ws.Copy will copy the first sheet from ThisWorkbook.Sheets and create a new workbook basically, as far as I understand. So then I have to refer to the new sheet in ActiveWorkbook (which would represent the first split off sheet from the master workbook that has them all). I just wanted to clarify what the script is doing, anyways, it's strange and I can't seem to figure it out, I would like to handle the errors 1004 and save them using file path of StoreDir2. Basically, when the code hits this line: ActiveWorkbook.SaveAs StoreDir & "Store_" & ws.Name & endoffilename & ".xls" It's supposed to save to the StoreDir found in the case statement and this usually works flawless, unless the directory folder got deleted, changed or wasn't made yet, then Excel can't save the file and results in error 1004 as far as I know. So I wanted to forward any errors to the Error Handling block: ErrHandler: So let's say I have 4 sheets to process in the ws, they are 1, 6000, 2, 5432 The 6000 and 5432 would not be found in the Case Else as a default address to use for numbers that don't exist, this works great. So the first time around for sheet with number 1 in it, that address would be found in the Case, but I deleted it's Store folder on purpose to create the error 1004 in Saving and to catch that and to process it into a different folder which is what StoreDir2 is. The first time around this works exactly, then next Store in 6000 that address will be gotten from Case Else, works.... Then Store 2, it generates error 1004, but this time the VBA just quits with the error 1004 and it dones't go to the error handling block. So that is my question, in the first loop it does, but on the 3rd loop when the 1004 error is encountered, it's not going to the ErrHandler: Why ? How can I make it better ? Any ideas anyone ? Sub Export_test() Dim ws As Worksheet Dim StoreDir, StoreDir2 As String Dim pg As Workbook Dim endoffilename As String Set pg = Workbooks.Open(Filename:="c:\path\temp_pg.xls") StoreDir2 = "c:\Documents and Settings\lanid\Desktop \save_file_1004_error\" endoffilename = InputBox("Enter the name of the Excel file name to be used: ") For Each ws In ThisWorkbook.Sheets ws.Copy StoreDir = ws.Name On Error GoTo ErrHandler Select Case StoreDir Case Is = 1, 2, 4, 5, 6, 9, 10, 12, 20, 21, 23, 24, 25, 26, 81, 82 StoreDir = "c:\Documents and Settings\lanid\Desktop\stores\" & ws.Name & "\" Case Else StoreDir = "c:\Documents and Settings\lanid\Desktop\no_match_store_dump \" End Select ActiveWorkbook.SaveAs StoreDir & "Store_" & ws.Name & endoffilename & ".xls" ActiveWorkbook.Close True 1: Next ErrHandler: If Err.Number = 1004 Then ActiveWorkbook.SaveAs StoreDir2 & "Store_" & ws.Name & endoffilename & ".xls" ActiveWorkbook.Close True GoTo 1 Else MsgBox "The message text of the error is: " & Error(Err) End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to catch error 1004, don't work in 2nd loop...
Every error handler must have a Resume:
ErrHandler: If Err.Number = 1004 Then ActiveWorkbook.SaveAs StoreDir2 & "Store_" & ws.Name & endoffilename & ".xls" ActiveWorkbook.Close True GoTo 1 ''<<<<<<< Use Resume instead. Else MsgBox "The message text of the error is: " & Error(Err) End If -- Jim "RompStar" wrote in message ... Hi there... ThisWorkbook.Sheets represents the workbook that has all the sheets that will be used for the export to different folders. ws.Copy will copy the first sheet from ThisWorkbook.Sheets and create a new workbook basically, as far as I understand. So then I have to refer to the new sheet in ActiveWorkbook (which would represent the first split off sheet from the master workbook that has them all). I just wanted to clarify what the script is doing, anyways, it's strange and I can't seem to figure it out, I would like to handle the errors 1004 and save them using file path of StoreDir2. Basically, when the code hits this line: ActiveWorkbook.SaveAs StoreDir & "Store_" & ws.Name & endoffilename & ".xls" It's supposed to save to the StoreDir found in the case statement and this usually works flawless, unless the directory folder got deleted, changed or wasn't made yet, then Excel can't save the file and results in error 1004 as far as I know. So I wanted to forward any errors to the Error Handling block: ErrHandler: So let's say I have 4 sheets to process in the ws, they are 1, 6000, 2, 5432 The 6000 and 5432 would not be found in the Case Else as a default address to use for numbers that don't exist, this works great. So the first time around for sheet with number 1 in it, that address would be found in the Case, but I deleted it's Store folder on purpose to create the error 1004 in Saving and to catch that and to process it into a different folder which is what StoreDir2 is. The first time around this works exactly, then next Store in 6000 that address will be gotten from Case Else, works.... Then Store 2, it generates error 1004, but this time the VBA just quits with the error 1004 and it dones't go to the error handling block. So that is my question, in the first loop it does, but on the 3rd loop when the 1004 error is encountered, it's not going to the ErrHandler: Why ? How can I make it better ? Any ideas anyone ? Sub Export_test() Dim ws As Worksheet Dim StoreDir, StoreDir2 As String Dim pg As Workbook Dim endoffilename As String Set pg = Workbooks.Open(Filename:="c:\path\temp_pg.xls") StoreDir2 = "c:\Documents and Settings\lanid\Desktop \save_file_1004_error\" endoffilename = InputBox("Enter the name of the Excel file name to be used: ") For Each ws In ThisWorkbook.Sheets ws.Copy StoreDir = ws.Name On Error GoTo ErrHandler Select Case StoreDir Case Is = 1, 2, 4, 5, 6, 9, 10, 12, 20, 21, 23, 24, 25, 26, 81, 82 StoreDir = "c:\Documents and Settings\lanid\Desktop\stores\" & ws.Name & "\" Case Else StoreDir = "c:\Documents and Settings\lanid\Desktop\no_match_store_dump \" End Select ActiveWorkbook.SaveAs StoreDir & "Store_" & ws.Name & endoffilename & ".xls" ActiveWorkbook.Close True 1: Next ErrHandler: If Err.Number = 1004 Then ActiveWorkbook.SaveAs StoreDir2 & "Store_" & ws.Name & endoffilename & ".xls" ActiveWorkbook.Close True GoTo 1 Else MsgBox "The message text of the error is: " & Error(Err) End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to catch error 1004, don't work in 2nd loop...
So should I use Resume 1
or just resume ? because I would like it to resume at a specific location, as it would try to execute the next line of code and I want it to start at the top of the loop. RompStar On Apr 15, 4:56*pm, "Jim Rech" wrote: Every error handler must have a Resume: ErrHandler: If Err.Number = 1004 Then ActiveWorkbook.SaveAs StoreDir2 & "Store_" & ws.Name & endoffilename & ".xls" ActiveWorkbook.Close True GoTo 1 *''<<<<<<< Use Resume instead. Else MsgBox "The message text of the error is: " & Error(Err) End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error 1004 in loop | Excel Discussion (Misc queries) | |||
run time error 1004 in loop | Excel Discussion (Misc queries) | |||
Copy Worksheets Error #1004 Work Around | Excel Programming | |||
Simple For-Loop gives 1004 error using variable to EntireRow.Delete | Excel Programming |