How do I correctly identify file in VBA Dir Loop?

May 3, 2022

The goal here is to loop through a folder with a few thousand .txt files and extract a few pieces of information about each file (from within the text) to a spreadsheet.

When I run if, I get Run-time error ’53’ – File Not Found error at Line 21 (Open FileName For Input As #FileNum).

When I run the loop to import the text for a single file (includes that line), it runs fine. But when I try to add the outer loop to command it to loop through all the files, I get the error. I’m not sure how to resolve it.

Sub TextDataLoop()
    Dim FilePath As String
    Dim Sh As Worksheet
    Dim FileName As String
    Dim FileNum As Integer
    Dim r As Long
    Dim Data As String
    Dim Txt As String
    
    FilePath = "I:\ArchivedCustomerOrders\"
    Set Sh = Worksheets("Sheet1")

    FileName = Dir(FilePath & "*.txt")
    FileNum = FreeFile
    r = 2
    
    Do While Right(FilePath, 3) > 0
        Open FileName For Input As #FileNum
        
        Do While Not EOF(FileNum)
            Line Input #FileNum, Data
            Txt = Txt & Join(Split(Data, vbTab), " ") & " "
        Loop
                
        Sh.Cells(r, 1).Value = FileName
        Sh.Cells(r, 2).Value = Trim(Mid(Txt, 95, 7))
        Sh.Cells(r, 3).Value = Trim(Mid(Txt, 122, 9))
        Sh.Cells(r, 4).Value = Trim(Mid(Txt, 991, 5))
        
        Close #FileNum
        
        r = r + 1    
    Loop
End Sub

>Solution :

Several issues here:
a) Dir returns only the file name, not the whole path. You need to specify the path together with the file name:

Open FilePath & FileName For Input As #FileNum

b) When you want to loop over all files, you will need to issue a Dir (without parameter) at the end of the loop so you can continue with the next file.

c) You check the FilePath-Variable in your Do-While condition, but that will never change. You will need to check the Variable FileName.

 FileName = Dir(FilePath & "*.txt")
 Do While FileName <> ""        ' Loop while there are still files.
    Open FilePath & FileName For Input As #FileNum
    ... (do your file handling here) ...
    Close #FileNum
    
    r = r + 1    
    FileName = Dir   ' Get the name of the next file
Loop