LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
bac bac is offline
external usenet poster
 
Posts: 76
Default 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



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trapping errors in a VLOOKUP function Blue Max Excel Worksheet Functions 1 April 26th 09 03:03 AM
GetOpenFilename - Trapping Errors Tim Childs Excel Programming 5 April 9th 06 03:58 PM
Error Trapping Still Not Working Ken[_18_] Excel Programming 5 May 17th 04 11:24 PM
Error Trapping Still Not Working Ken[_18_] Excel Programming 0 May 17th 04 06:51 PM
Remind me to set Error Trapping to Break on Unhandled Errors don Excel Programming 0 November 5th 03 06:06 PM


All times are GMT +1. The time now is 06:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"