|
Error trapping is not working for errors related to files
Windows XP pro SP2; MSO Pro 2003
the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ....Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops ......Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
Error trapping is not working for errors related to files
The way error handling typically is setup is to be recorded at the bottom
portion of the method such as the following. Error handling is done within the method unless it's handled by a method that called on the method either directly or indirectly via the call path: Sub Button5_Click() Dim lngErrorCode As Long OnError Goto ErrorHandle . . . . lngErrorCode = 99 'Do things with the directory. If it doesn't exist, error handling stack will create directory. 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir Set fs = CreateObject("Scripting.FileSystemObject") 'If code errors out on the Change Directory, it will goto the Error Stack, create the folder 'then resume back to where it left off in regards to the error. lngErrorCode = 72 fs.ChDir DR_Name lngErrorCode = 99 'Run the file generator here. ExitSub: 'Clean up code may be put in between this line and the "Exit Sub" line. Exit Sub ErrorHandle: Select Case lngErrorCode Case 72 Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir fs.CreateFolder DR_Name 'Create the directory Err.Clear Resume Case Else MsgBox "Oops, an unexpected error has taken place. Ending program.",48 Err.Clear Resume ExitSub Select End End Sub -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... Windows XP pro SP2; MSO Pro 2003 the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ...Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops .....Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
Error trapping is not working for errors related to files
If you'll check my OP code you'll see that I have an error handler (I call it
oops:) that functions as you describe and control should go to this Error Handler with an error 1004 or any other error (On Error Goto oops) but control is apparently returning to Excel or VBA or the compiler or somewhere else as I do not get the error handler response (i.e. the msg box). In addition the On error Goto lable option is fully documented and acceptable usage for trapping and responding to errors. Your solution reacts to where the error occurred not what the error was. By changing the value of your lngerrorcode before and after the ChDir and testing in the error handler for = 72, you only know the ChDir failed, but not why. That's why the case statements within the error handler should test for the err.number. Did the chDir fail because the file does not exist, because access was denied, because Excel couldn't find the path? The specific error should determine a specific response. Your suggestion is fine for locating where the error occurred (which ChDir failed if there are more than 1 in you app), but does not address the why it failed. in any event, clearly the error flag is either not being raised, forcing control into my errorhandler, something else is passing the error back to VBA rather than into the error handler. "Ronald Dodge" wrote: The way error handling typically is setup is to be recorded at the bottom portion of the method such as the following. Error handling is done within the method unless it's handled by a method that called on the method either directly or indirectly via the call path: Sub Button5_Click() Dim lngErrorCode As Long OnError Goto ErrorHandle . . . . lngErrorCode = 99 'Do things with the directory. If it doesn't exist, error handling stack will create directory. 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir Set fs = CreateObject("Scripting.FileSystemObject") 'If code errors out on the Change Directory, it will goto the Error Stack, create the folder 'then resume back to where it left off in regards to the error. lngErrorCode = 72 fs.ChDir DR_Name lngErrorCode = 99 'Run the file generator here. ExitSub: 'Clean up code may be put in between this line and the "Exit Sub" line. Exit Sub ErrorHandle: Select Case lngErrorCode Case 72 Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir fs.CreateFolder DR_Name 'Create the directory Err.Clear Resume Case Else MsgBox "Oops, an unexpected error has taken place. Ending program.",48 Err.Clear Resume ExitSub Select End End Sub -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... Windows XP pro SP2; MSO Pro 2003 the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ...Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops .....Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
Error trapping is not working for errors related to files
Your error handling code is outside of the procedure, and the Goto statement
only works within the procedure, which is why it's not working properly. Anotherwords: When you use "OnError Goto Oops", the "Oops:" label must be within the same procedure in between the "Sub" and "End Sub" lines. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... If you'll check my OP code you'll see that I have an error handler (I call it oops:) that functions as you describe and control should go to this Error Handler with an error 1004 or any other error (On Error Goto oops) but control is apparently returning to Excel or VBA or the compiler or somewhere else as I do not get the error handler response (i.e. the msg box). In addition the On error Goto lable option is fully documented and acceptable usage for trapping and responding to errors. Your solution reacts to where the error occurred not what the error was. By changing the value of your lngerrorcode before and after the ChDir and testing in the error handler for = 72, you only know the ChDir failed, but not why. That's why the case statements within the error handler should test for the err.number. Did the chDir fail because the file does not exist, because access was denied, because Excel couldn't find the path? The specific error should determine a specific response. Your suggestion is fine for locating where the error occurred (which ChDir failed if there are more than 1 in you app), but does not address the why it failed. in any event, clearly the error flag is either not being raised, forcing control into my errorhandler, something else is passing the error back to VBA rather than into the error handler. "Ronald Dodge" wrote: The way error handling typically is setup is to be recorded at the bottom portion of the method such as the following. Error handling is done within the method unless it's handled by a method that called on the method either directly or indirectly via the call path: Sub Button5_Click() Dim lngErrorCode As Long OnError Goto ErrorHandle . . . . lngErrorCode = 99 'Do things with the directory. If it doesn't exist, error handling stack will create directory. 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir Set fs = CreateObject("Scripting.FileSystemObject") 'If code errors out on the Change Directory, it will goto the Error Stack, create the folder 'then resume back to where it left off in regards to the error. lngErrorCode = 72 fs.ChDir DR_Name lngErrorCode = 99 'Run the file generator here. ExitSub: 'Clean up code may be put in between this line and the "Exit Sub" line. Exit Sub ErrorHandle: Select Case lngErrorCode Case 72 Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir fs.CreateFolder DR_Name 'Create the directory Err.Clear Resume Case Else MsgBox "Oops, an unexpected error has taken place. Ending program.",48 Err.Clear Resume ExitSub Select End End Sub -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... Windows XP pro SP2; MSO Pro 2003 the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ...Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops .....Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
Error trapping is not working for errors related to files
Just a guess, but in the vbe with this project active (selected in the
project explorer), if you go to tools=Options, then the general tab, under error trapping, is break on all errors checked. If so, it should be one of the other choices. -- Regards, Tom Ogilvy "BAC" wrote: If you'll check my OP code you'll see that I have an error handler (I call it oops:) that functions as you describe and control should go to this Error Handler with an error 1004 or any other error (On Error Goto oops) but control is apparently returning to Excel or VBA or the compiler or somewhere else as I do not get the error handler response (i.e. the msg box). In addition the On error Goto lable option is fully documented and acceptable usage for trapping and responding to errors. Your solution reacts to where the error occurred not what the error was. By changing the value of your lngerrorcode before and after the ChDir and testing in the error handler for = 72, you only know the ChDir failed, but not why. That's why the case statements within the error handler should test for the err.number. Did the chDir fail because the file does not exist, because access was denied, because Excel couldn't find the path? The specific error should determine a specific response. Your suggestion is fine for locating where the error occurred (which ChDir failed if there are more than 1 in you app), but does not address the why it failed. in any event, clearly the error flag is either not being raised, forcing control into my errorhandler, something else is passing the error back to VBA rather than into the error handler. "Ronald Dodge" wrote: The way error handling typically is setup is to be recorded at the bottom portion of the method such as the following. Error handling is done within the method unless it's handled by a method that called on the method either directly or indirectly via the call path: Sub Button5_Click() Dim lngErrorCode As Long OnError Goto ErrorHandle . . . . lngErrorCode = 99 'Do things with the directory. If it doesn't exist, error handling stack will create directory. 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir Set fs = CreateObject("Scripting.FileSystemObject") 'If code errors out on the Change Directory, it will goto the Error Stack, create the folder 'then resume back to where it left off in regards to the error. lngErrorCode = 72 fs.ChDir DR_Name lngErrorCode = 99 'Run the file generator here. ExitSub: 'Clean up code may be put in between this line and the "Exit Sub" line. Exit Sub ErrorHandle: Select Case lngErrorCode Case 72 Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir fs.CreateFolder DR_Name 'Create the directory Err.Clear Resume Case Else MsgBox "Oops, an unexpected error has taken place. Ending program.",48 Err.Clear Resume ExitSub Select End End Sub -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... Windows XP pro SP2; MSO Pro 2003 the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ...Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops .....Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
Error trapping is not working for errors related to files
look again..It's right after the exit sub, before End Sub.
The errorHanler and oops: label are within the procedure. "Ronald Dodge" wrote: Your error handling code is outside of the procedure, and the Goto statement only works within the procedure, which is why it's not working properly. Anotherwords: When you use "OnError Goto Oops", the "Oops:" label must be within the same procedure in between the "Sub" and "End Sub" lines. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... If you'll check my OP code you'll see that I have an error handler (I call it oops:) that functions as you describe and control should go to this Error Handler with an error 1004 or any other error (On Error Goto oops) but control is apparently returning to Excel or VBA or the compiler or somewhere else as I do not get the error handler response (i.e. the msg box). In addition the On error Goto lable option is fully documented and acceptable usage for trapping and responding to errors. Your solution reacts to where the error occurred not what the error was. By changing the value of your lngerrorcode before and after the ChDir and testing in the error handler for = 72, you only know the ChDir failed, but not why. That's why the case statements within the error handler should test for the err.number. Did the chDir fail because the file does not exist, because access was denied, because Excel couldn't find the path? The specific error should determine a specific response. Your suggestion is fine for locating where the error occurred (which ChDir failed if there are more than 1 in you app), but does not address the why it failed. in any event, clearly the error flag is either not being raised, forcing control into my errorhandler, something else is passing the error back to VBA rather than into the error handler. "Ronald Dodge" wrote: The way error handling typically is setup is to be recorded at the bottom portion of the method such as the following. Error handling is done within the method unless it's handled by a method that called on the method either directly or indirectly via the call path: Sub Button5_Click() Dim lngErrorCode As Long OnError Goto ErrorHandle . . . . lngErrorCode = 99 'Do things with the directory. If it doesn't exist, error handling stack will create directory. 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir Set fs = CreateObject("Scripting.FileSystemObject") 'If code errors out on the Change Directory, it will goto the Error Stack, create the folder 'then resume back to where it left off in regards to the error. lngErrorCode = 72 fs.ChDir DR_Name lngErrorCode = 99 'Run the file generator here. ExitSub: 'Clean up code may be put in between this line and the "Exit Sub" line. Exit Sub ErrorHandle: Select Case lngErrorCode Case 72 Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir fs.CreateFolder DR_Name 'Create the directory Err.Clear Resume Case Else MsgBox "Oops, an unexpected error has taken place. Ending program.",48 Err.Clear Resume ExitSub Select End End Sub -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... Windows XP pro SP2; MSO Pro 2003 the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ...Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops .....Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
Error trapping is not working for errors related to files
"Break on Unhandled Errors" is checked.
"Tom Ogilvy" wrote: Just a guess, but in the vbe with this project active (selected in the project explorer), if you go to tools=Options, then the general tab, under error trapping, is break on all errors checked. If so, it should be one of the other choices. -- Regards, Tom Ogilvy "BAC" wrote: If you'll check my OP code you'll see that I have an error handler (I call it oops:) that functions as you describe and control should go to this Error Handler with an error 1004 or any other error (On Error Goto oops) but control is apparently returning to Excel or VBA or the compiler or somewhere else as I do not get the error handler response (i.e. the msg box). In addition the On error Goto lable option is fully documented and acceptable usage for trapping and responding to errors. Your solution reacts to where the error occurred not what the error was. By changing the value of your lngerrorcode before and after the ChDir and testing in the error handler for = 72, you only know the ChDir failed, but not why. That's why the case statements within the error handler should test for the err.number. Did the chDir fail because the file does not exist, because access was denied, because Excel couldn't find the path? The specific error should determine a specific response. Your suggestion is fine for locating where the error occurred (which ChDir failed if there are more than 1 in you app), but does not address the why it failed. in any event, clearly the error flag is either not being raised, forcing control into my errorhandler, something else is passing the error back to VBA rather than into the error handler. "Ronald Dodge" wrote: The way error handling typically is setup is to be recorded at the bottom portion of the method such as the following. Error handling is done within the method unless it's handled by a method that called on the method either directly or indirectly via the call path: Sub Button5_Click() Dim lngErrorCode As Long OnError Goto ErrorHandle . . . . lngErrorCode = 99 'Do things with the directory. If it doesn't exist, error handling stack will create directory. 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir Set fs = CreateObject("Scripting.FileSystemObject") 'If code errors out on the Change Directory, it will goto the Error Stack, create the folder 'then resume back to where it left off in regards to the error. lngErrorCode = 72 fs.ChDir DR_Name lngErrorCode = 99 'Run the file generator here. ExitSub: 'Clean up code may be put in between this line and the "Exit Sub" line. Exit Sub ErrorHandle: Select Case lngErrorCode Case 72 Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir fs.CreateFolder DR_Name 'Create the directory Err.Clear Resume Case Else MsgBox "Oops, an unexpected error has taken place. Ending program.",48 Err.Clear Resume ExitSub Select End End Sub -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... Windows XP pro SP2; MSO Pro 2003 the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ...Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops .....Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
Error trapping is not working for errors related to files
My mistake, but anyhow, try setting up an ExitSub label, then use "Resume
ExitSub" in place of "Exit Sub" in your error handling, so as it gets out of the error handling process. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... look again..It's right after the exit sub, before End Sub. The errorHanler and oops: label are within the procedure. "Ronald Dodge" wrote: Your error handling code is outside of the procedure, and the Goto statement only works within the procedure, which is why it's not working properly. Anotherwords: When you use "OnError Goto Oops", the "Oops:" label must be within the same procedure in between the "Sub" and "End Sub" lines. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... If you'll check my OP code you'll see that I have an error handler (I call it oops:) that functions as you describe and control should go to this Error Handler with an error 1004 or any other error (On Error Goto oops) but control is apparently returning to Excel or VBA or the compiler or somewhere else as I do not get the error handler response (i.e. the msg box). In addition the On error Goto lable option is fully documented and acceptable usage for trapping and responding to errors. Your solution reacts to where the error occurred not what the error was. By changing the value of your lngerrorcode before and after the ChDir and testing in the error handler for = 72, you only know the ChDir failed, but not why. That's why the case statements within the error handler should test for the err.number. Did the chDir fail because the file does not exist, because access was denied, because Excel couldn't find the path? The specific error should determine a specific response. Your suggestion is fine for locating where the error occurred (which ChDir failed if there are more than 1 in you app), but does not address the why it failed. in any event, clearly the error flag is either not being raised, forcing control into my errorhandler, something else is passing the error back to VBA rather than into the error handler. "Ronald Dodge" wrote: The way error handling typically is setup is to be recorded at the bottom portion of the method such as the following. Error handling is done within the method unless it's handled by a method that called on the method either directly or indirectly via the call path: Sub Button5_Click() Dim lngErrorCode As Long OnError Goto ErrorHandle . . . . lngErrorCode = 99 'Do things with the directory. If it doesn't exist, error handling stack will create directory. 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir Set fs = CreateObject("Scripting.FileSystemObject") 'If code errors out on the Change Directory, it will goto the Error Stack, create the folder 'then resume back to where it left off in regards to the error. lngErrorCode = 72 fs.ChDir DR_Name lngErrorCode = 99 'Run the file generator here. ExitSub: 'Clean up code may be put in between this line and the "Exit Sub" line. Exit Sub ErrorHandle: Select Case lngErrorCode Case 72 Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir fs.CreateFolder DR_Name 'Create the directory Err.Clear Resume Case Else MsgBox "Oops, an unexpected error has taken place. Ending program.",48 Err.Clear Resume ExitSub Select End End Sub -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... Windows XP pro SP2; MSO Pro 2003 the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ...Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops .....Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
Error trapping is not working for errors related to files
replace err.clear with
On Error goto Oops -- Regards, Tom Ogilvy "BAC" wrote: "Break on Unhandled Errors" is checked. "Tom Ogilvy" wrote: Just a guess, but in the vbe with this project active (selected in the project explorer), if you go to tools=Options, then the general tab, under error trapping, is break on all errors checked. If so, it should be one of the other choices. -- Regards, Tom Ogilvy "BAC" wrote: If you'll check my OP code you'll see that I have an error handler (I call it oops:) that functions as you describe and control should go to this Error Handler with an error 1004 or any other error (On Error Goto oops) but control is apparently returning to Excel or VBA or the compiler or somewhere else as I do not get the error handler response (i.e. the msg box). In addition the On error Goto lable option is fully documented and acceptable usage for trapping and responding to errors. Your solution reacts to where the error occurred not what the error was. By changing the value of your lngerrorcode before and after the ChDir and testing in the error handler for = 72, you only know the ChDir failed, but not why. That's why the case statements within the error handler should test for the err.number. Did the chDir fail because the file does not exist, because access was denied, because Excel couldn't find the path? The specific error should determine a specific response. Your suggestion is fine for locating where the error occurred (which ChDir failed if there are more than 1 in you app), but does not address the why it failed. in any event, clearly the error flag is either not being raised, forcing control into my errorhandler, something else is passing the error back to VBA rather than into the error handler. "Ronald Dodge" wrote: The way error handling typically is setup is to be recorded at the bottom portion of the method such as the following. Error handling is done within the method unless it's handled by a method that called on the method either directly or indirectly via the call path: Sub Button5_Click() Dim lngErrorCode As Long OnError Goto ErrorHandle . . . . lngErrorCode = 99 'Do things with the directory. If it doesn't exist, error handling stack will create directory. 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir Set fs = CreateObject("Scripting.FileSystemObject") 'If code errors out on the Change Directory, it will goto the Error Stack, create the folder 'then resume back to where it left off in regards to the error. lngErrorCode = 72 fs.ChDir DR_Name lngErrorCode = 99 'Run the file generator here. ExitSub: 'Clean up code may be put in between this line and the "Exit Sub" line. Exit Sub ErrorHandle: Select Case lngErrorCode Case 72 Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir fs.CreateFolder DR_Name 'Create the directory Err.Clear Resume Case Else MsgBox "Oops, an unexpected error has taken place. Ending program.",48 Err.Clear Resume ExitSub Select End End Sub -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... Windows XP pro SP2; MSO Pro 2003 the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ...Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops .....Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
Error trapping is not working for errors related to files
In your error catching code, where you have:
On Error, Goto mk_dir That is where you should have all of your errors go to a single error handling stacker, which that one is outside of the error handling stack. If you put that in the error handling stack similar to my example, and then use the Resume keyword, that would allow the code to create the directory as needed, which then go back to run it. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... look again..It's right after the exit sub, before End Sub. The errorHanler and oops: label are within the procedure. "Ronald Dodge" wrote: Your error handling code is outside of the procedure, and the Goto statement only works within the procedure, which is why it's not working properly. Anotherwords: When you use "OnError Goto Oops", the "Oops:" label must be within the same procedure in between the "Sub" and "End Sub" lines. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... If you'll check my OP code you'll see that I have an error handler (I call it oops:) that functions as you describe and control should go to this Error Handler with an error 1004 or any other error (On Error Goto oops) but control is apparently returning to Excel or VBA or the compiler or somewhere else as I do not get the error handler response (i.e. the msg box). In addition the On error Goto lable option is fully documented and acceptable usage for trapping and responding to errors. Your solution reacts to where the error occurred not what the error was. By changing the value of your lngerrorcode before and after the ChDir and testing in the error handler for = 72, you only know the ChDir failed, but not why. That's why the case statements within the error handler should test for the err.number. Did the chDir fail because the file does not exist, because access was denied, because Excel couldn't find the path? The specific error should determine a specific response. Your suggestion is fine for locating where the error occurred (which ChDir failed if there are more than 1 in you app), but does not address the why it failed. in any event, clearly the error flag is either not being raised, forcing control into my errorhandler, something else is passing the error back to VBA rather than into the error handler. "Ronald Dodge" wrote: The way error handling typically is setup is to be recorded at the bottom portion of the method such as the following. Error handling is done within the method unless it's handled by a method that called on the method either directly or indirectly via the call path: Sub Button5_Click() Dim lngErrorCode As Long OnError Goto ErrorHandle . . . . lngErrorCode = 99 'Do things with the directory. If it doesn't exist, error handling stack will create directory. 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir Set fs = CreateObject("Scripting.FileSystemObject") 'If code errors out on the Change Directory, it will goto the Error Stack, create the folder 'then resume back to where it left off in regards to the error. lngErrorCode = 72 fs.ChDir DR_Name lngErrorCode = 99 'Run the file generator here. ExitSub: 'Clean up code may be put in between this line and the "Exit Sub" line. Exit Sub ErrorHandle: Select Case lngErrorCode Case 72 Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir fs.CreateFolder DR_Name 'Create the directory Err.Clear Resume Case Else MsgBox "Oops, an unexpected error has taken place. Ending program.",48 Err.Clear Resume ExitSub Select End End Sub -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "BAC" wrote in message ... Windows XP pro SP2; MSO Pro 2003 the VBA stops with message Error '58' File already exists or Error '1004' Excel cannot access the file ...... Error 58 should not occur because if CHDir executes control should pass to runit:, bypassing the 'fs.CreateFolder DR_Name' statement If I 'step past' this error I get the VBA error message rather than the msgbox from the error handler Why do these errors not "trap" to the error handler at oops:? code: Sub Button5_Click() ...Dim Stuff... curpath = CStr(Sheets("Audit").Cells(2, 9).Value) On Error GoTo oops 'Turn off calculation to speed up processing With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False 'Get date of recon - used to generate file names being retrieved/saved strx = InputBox("Date of Reconciliation (e.g. 01/31/03):", "Reconciliation Date") If strx = "" Then Exit Sub 'test for directory: strdir = Format(strx, "yyyy_mm") DR_Name = "C:\WorkingFiles\FCSRecon\" & strdir 'Set up variables in case we have to create the folder Old_date = DateAdd("m", -1, strx) Old_dir = Format(Old_date, "yyyy_mm") Source_Name = "C:\WorkingFiles\FCS_Recon\" & Old_dir Set fs = CreateObject("Scripting.FileSystemObject") On Error GoTo mk_dir 'If we try to open Directory that does not exist, we get an error _ - this forces to location where we create the directory should that occur ChDir DR_Name 'We'll get an error here if the directory does not exist GoTo runit 'If we don't get an error, bypass the create directory code mk_dir: 'This is where we come to build directory if it doesn't exist because it gave _ - us an error when ChDir Err.Clear fs.CreateFolder DR_Name 'Create the directory runit: 'If the directory exists we come here to run file generator for FCSRecon.xls 'reset On error On Error GoTo oops .....Code..... Exit Sub 'Error Handler oops: Select Case Err.Number Case Is = 1004 'Unable to access file Err.Clear k = MsgBox("I was unable to find one of the files I need. Please verify required file names are properly formatted..", vbOKOnly, "Missing Files!") For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(2).Activate ActiveSheet.Range("A3").Select Exit Sub Case Is < 1004 'Any other error k = MsgBox("The following error occured.. Please try again.." & Err.Number & " " & Err.Description, vbCritical, "Error Message!!") Err.Clear For i = 3 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = True Then Sheets(i).Visible = False Else End If Next i Windows(recfil).Activate ActiveWorkbook.Sheets(1).Activate ActiveSheet.Range("A3").Select Exit Sub End Select End Sub |
| All times are GMT +1. The time now is 10:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com