Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Trapping errors in a VLOOKUP function | Excel Worksheet Functions | |||
GetOpenFilename - Trapping Errors | Excel Programming | |||
Error Trapping Still Not Working | Excel Programming | |||
Error Trapping Still Not Working | Excel Programming | |||
Remind me to set Error Trapping to Break on Unhandled Errors | Excel Programming |