CODEWITHSUNDEEP
excelvba
Brent Newman
Brent Newman

Reputation: 13

How do I identify file in VBA Dir Loop?

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

I get

Run-time error '53' - File Not Found error at Line 21

on Open FileName For Input As #FileNum.

I can run the loop to import the text for a single file (includes that line).

When I add the outer loop to loop through all the files, I get the error.

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

Upvotes: 0

Views: 76

Answers (2)

FunThomas
FunThomas

Reputation: 29171

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

Upvotes: 1

CHill60
CHill60

Reputation: 1958

You only have the file NAME - if you want to open the file, use the fully qualified name including the path

Do While Right(FilePath, 3) > 0
    Open FilePath & FileName For Input As #FileNum

Upvotes: 0

Related Questions