Checking if multiple text files exist before attempting import into Excel

[mrpavlos]

Hello all

Many thanks for this great forum, and the assistance given to me in the past. I have another question that I am not sure on the answer to.

I have a workbook that will import in as text four certain files from a directory that itself will contain many other files in addition to these four. Lets name these particular files as A.txt, B.txt, C.txt, D.txt.
Each of these imports will be done in turn in the same sub and will be imported each to their own worksheet in the destination workbook.

I would like before I try the import of these files to check firstly if these four source files do indeed exist in their directory. I understand that I can check for the existence of a single file by using say the example code below:

Sub TestFl()
'VBA Check if File Exists
Dim strFileA As String
strFileA = "C:\Users\Data\RawData\A.txt"

If Len(Dir(strFileA))=0 Then
MsgBox "File Does Not Exist"
Else
MsgBox "File Exists"
End If
End Sub

My question would be how could I check for the existence of the four files I want to import. Would the following example work, or would it be too inefficient?

Dim strFileA As String
Dim strFileB As String
Dim strFileC As String
Dim strFileD As String

strFileA = "C:\Users\Data\RawData\A.txt"
strFileB = "C:\Users\Data\RawData\B.txt"
strFileC = "C:\Users\Data\RawData\C.txt"
strFileD = "C:\Users\Data\RawData\D.txt"


If (Len(Dir(strFileA))=0 Or Len(Dir(strFileB))=0 Or Len(Dir(strFileC))=0 Or Len(Dir(strFileD))=0) Then
MsgBox "One of the source files is missing"


Many thanks for taking the time to look at this and for any guidance or advice you can give,
Paul

 

[jasonb75]

It should work, given that you need to test the existence of all 4 files, you're not going to be able to do it much more efficiently.

Personally, I would make it a little more informative though by identifying the missing file(s) to the user.

If (Len(Dir(strFileA))=0 Then
    MsgBox strFileA & " is Missing!", vbCritical
ElseIf (Len(Dir(strFileB))=0 Then
    MsgBox strFileB & " is Missing!", vbCritical
ElseIf (Len(Dir(strFileC))=0 Then
    MsgBox strFileC & " is Missing!", vbCritical
ElseIf (Len(Dir(strFileD))=0 Then
    MsgBox strFileD & " is Missing!", vbCritical
Else
    ' Do stuff with the files
End If

 

[juddaaaa]

I'd use a function to return a boolean like this

Function AllFilesExist(Path As String, Files As Variant) As Boolean
  Dim fl As Variant
  AllFilesExist = True
  
  For Each fl In Files
    If Dir(Path & "\" & fl) = "" Then AllFilesExist = False
  Next fl
End Function

Sub MyMacro()
  If AllFilesExist("C:\Users\Data\RawData", Array("A.txt", "B.txt", "C.txt", "D.txt")) Then
    '// REST OF CODE HERE
  Else
    MsgBox "One or more files is missing!"
  End If
End Sub

 

[mrpavlos]

Cheers jasonb and juddaaaa. I really appreciate your time in looking at this and the advice give and the two different approaches. Again I am learning something new. Many thanks!

 

[juddaaaa]

You're welcome. Thanks for the feedback