Error Handling Missing Files in Import Process

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Maybe something like this.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top