Checking if multiple text files exist before attempting import into Excel

mrpavlos

New Member
Joined
Jul 28, 2018
Messages
23
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,584
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

Board Regular
Joined
Jan 4, 2020
Messages
208
Office Version
  1. 365
Platform
  1. Windows
I'd use a function to return a boolean like this
VBA Code:
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

New Member
Joined
Jul 28, 2018
Messages
23
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

Board Regular
Joined
Jan 4, 2020
Messages
208
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,755
Messages
5,574,043
Members
412,565
Latest member
roberttaekim
Top