Tweak Error Handling if File Not Found

[reberryjr]

I've been working on this particular issue for about 5 hours and cannot seem to find the correct syntax. I have a command button that when clicked, launches a module. That module then calls ~30 other modules; all of which tap out to a shared drive and ingest data from other workbooks. I need to account for the fact that when this is run each month, not every file will be present in the shared drive. I've tried a couple variations of the underlying snippet, but this one seems to get me the closest. If the file is present, the code runs as expected. However, if the file isn't present, it's launching an error dialogue box at

Set s = Workbooks.Open(fP & fN)

Dim m As Workbook, s As Workbook
Dim mD As Worksheet, sD As Worksheet
Dim fP As String, fN As String, fE As String
Dim Hdr As Range, c As Range
Dim mDLR As Long, mNLR As Long, sDLR As Long

Set m = ThisWorkbook
Set mD = m.Sheets("New Data")

mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row

fP = "C:\Users\Import Files\"
fN = "LMemo"
fN = Dir(fP & fN & "*.xlsx")

If Len((Dir(fP & fN))) < 1 Then GoTo MissingFile

    Set s = Workbooks.Open(fP & fN) 'Error dialogue box is showing here.  

'My code is here.    
   
MissingFile:
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[dmt32]

Hi
untested but try this update to your code & see if does what you want

Sub reberryjr()
    Dim m           As Workbook, s      As Workbook
    Dim mD          As Worksheet, sD    As Worksheet
    Dim fP          As String, fN       As String, fE As String
    Dim Hdr         As Range, c         As Range
    Dim mDLR        As Long, mNLR       As Long, sDLR As Long
    
    Set m = ThisWorkbook
    Set mD = m.Sheets("New Data")
    
    mDLR = mD.Range("A" & mD.Rows.Count).End(xlUp).Row
    
    fP = "C:\Users\Import Files\"
    
    fN = fP & "LMemo.xlsx"
    
    On Error GoTo MissingFile
    If Len(Dir(fN, vbDirectory)) > 0 Then
        With Application
            .ScreenUpdating = False: .DisplayAlerts = False
        End With
        
        Set s = Workbooks.Open(fN, 0, False)
        
        'My code is here.
    Else
        'file not found
        Err.Raise 53
        
    End If
    
MissingFile:
    With Application
        .ScreenUpdating = True: .DisplayAlerts = True
    End With
    
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    
End Sub

Dave

 

[reberryjr]

Hi
untested but try this update to your code & see if does what you want

Sub reberryjr()
    Dim m           As Workbook, s      As Workbook
    Dim mD          As Worksheet, sD    As Worksheet
    Dim fP          As String, fN       As String, fE As String
    Dim Hdr         As Range, c         As Range
    Dim mDLR        As Long, mNLR       As Long, sDLR As Long
   
    Set m = ThisWorkbook
    Set mD = m.Sheets("New Data")
   
    mDLR = mD.Range("A" & mD.Rows.Count).End(xlUp).Row
   
    fP = "C:\Users\Import Files\"
   
    fN = fP & "LMemo.xlsx"
   
    On Error GoTo MissingFile
    If Len(Dir(fN, vbDirectory)) > 0 Then
        With Application
            .ScreenUpdating = False: .DisplayAlerts = False
        End With
       
        Set s = Workbooks.Open(fN, 0, False)
       
        'My code is here.
    Else
        'file not found
        Err.Raise 53
       
    End If
   
MissingFile:
    With Application
        .ScreenUpdating = True: .DisplayAlerts = True
    End With
   
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
   
End Sub

Dave

@dmt32 this worked on one of my import subs. Before I implement it on the others, what does Err.Raise 53 do/mean? I've not seen that before.

 

[dmt32]

@dmt32 this worked on one of my import subs. Before I implement it on the others, what does Err.Raise 53 do/mean? I've not seen that before.

I have added an error handling routine to your code.
By Enabling an error-handling routine and specifying the location to send the error(s) this should manage all errors in your code.

Where a file is not found by the Dir function I raised the error myself using the Raise Method
this action sends the code to your label & displays the msgbox with the specified error. Such and approach is preferable to using the Goto statement on its own which, outside use in error handlers, is widely regarded as a poor programming practice and should be avoided.

Dave

 

[reberryjr]

I have added an error handling routine to your code.
By Enabling an error-handling routine and specifying the location to send the error(s) this should manage all errors in your code.

Where a file is not found by the Dir function I raised the error myself using the Raise Method
this action sends the code to your label & displays the msgbox with the specified error. Such and approach is preferable to using the Goto statement on its own which, outside use in error handlers, is widely regarded as a poor programming practice and should be avoided.

Dave

I've been tasked with some more complicated things than what I'm used to, so I'm going to have to look into error handling deeper. I appreciate your assistance!

 

[dmt32]

I've been tasked with some more complicated things than what I'm used to, so I'm going to have to look into error handling deeper. I appreciate your assistance!

you are welcome glad suggestion helps
There are plenty of sites like this one Error Handling that provide useful guidance.

Dave