Error Handling Missing Files in Import Process

[reberryjr]

I have a module that calls several other modules that import data each month. The issue is that not all of the files will be present each month. I've been trying to put code in place that will exit an import module if the file isn't found, but I'm not having luck. I've tried a few things I found on the web, but none of them have worked.

Sub ImportLData()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

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\\Desktop\Data\Import Files\"
fN = "LData"
fN = Dir(fP & fN & "*.xlsx")

'If s Is Nothing Then Exit Sub - This just skips everything underneath it, even if the file is present.

Set s = Workbooks.Open(fP & fN)

'If s Is Nothing Then Exit Sub - This results in an error saying the fP can't be found.

On Error GoTo MissingFile - This results in an error saying the fP can't be found.

Set sD = s.Sheets("Accounts")

'If s Is Nothing Then Exit Sub - This results in an error saying the fP can't be found.

sDLR = sD.Range("A" & Rows.Count).End(xlUp).Row

sD.Activate

'***Do Stuff Here***

s.Close SaveChanges:=False

MissingFile:
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[rlv01]

Maybe something like this.

Sub ImportLData()
    Dim m As Workbook, s As Workbook
    Dim mD As Worksheet, sD As Worksheet
    Dim fP As String, fN As String
    Dim sDLR As Long
    Dim FFolder As Object, FFile As Object, FFound As Boolean
    
    fP = "C:\Users\\Desktop\Data\Import Files\"
    fN = "LData"
    
    With CreateObject("Scripting.FileSystemObject") 'use FileSystemObject (late binding)
        If Not .FolderExists(fP) Then
            MsgBox "Folder '" & fP & "' not found"
            Exit Sub
        End If
                
        Set m = ThisWorkbook
        Set mD = m.Sheets("New Data")
        Set FFolder = .getfolder(fP)
        
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        
        For Each FFile In FFolder.Files 'loop through files
            If Left(FFile.Name, Len(fN)) = fN And .GetExtensionName(FFile.Path) = "xlsx" Then
                FFound = True
                
                Set s = Workbooks.Open(FFile.Path)
                Set sD = s.Sheets("Accounts")
                sDLR = sD.Range("A" & Rows.Count).End(xlUp).Row
                sD.Activate
                
                '***Do Stuff Here***
                
                
                
                '***End Do Stuff ***
                
                s.Close SaveChanges:=False
                Set s = Nothing
                DoEvents
            End If
        Next FFile
    End With
    
    If Not FFound Then
        MsgBox "No files matching '" & fP & fN & "*.xlsx' were found"
    End If
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub