Error trapping is not working for errors related to files

[Guest]

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

 

[Ronald Dodge]

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

 

[Guest]

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]

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

 

[Guest]

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.

 

[Guest]

look again..It's right after the exit sub, before End Sub.

The errorHanler and oops: label are within the procedure.

 

[Guest]

"Break on Unhandled Errors" is checked.

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.

 

[Ronald Dodge]

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

 

[Guest]

replace err.clear with

On Error goto Oops

 

[Ronald Dodge]

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