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
CodePudding user response:
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
CodePudding user response:
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