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
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 = 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