Looping through text files

Question

I was wondering if anyone could assist please. I have created a logger in which multiple users are going into excel and a userform will log all the details. Each user has a separate file and then subsequently a master sheet will pull loop through their excel sheets for the data and pull it altogether.

We now have a new system that will pull through text files (unfortunately only txt files).

I have now set up all users to export their individual data as a txt file. However I am having trouble now with the mastersheet looping through the data and adding the next user underneath.

My old code is:

Sub Theloopofloops()

Dim wbk As Workbook
Dim Filename As String
Dim path As String
Dim rCell As Range
Dim wsO As Worksheet

path = "L:\MK\Logger\"
Filename = Dir(path & "*.xlsm")
Set wsO = ThisWorkbook.Sheets("Master")

Do While Len(Filename) > 0
    DoEvents
    Set wbk = Workbooks.Open(path & Filename, True, True)
        For Each rCell In wbk.Worksheets("Calls").Range("A2:A2000").Cells
            If rCell <> "" And rCell.Value <> vbNullString And rCell.Value <> 0 Then
                wsO.Cells(wsO.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(, 16).Value = rCell.Resize(, 16).Value
            End If
        Next rCell
    wbk.Close False
    Filename = Dir
Loop

Obviously this was looping through workbooks. Any assistance would be greatly appreciated.

Thanks,

Check out the TextStream functionality of the FSO. It says VBScript, but it's relevant to VBA too https://msdn.microsoft.com/en-us/library/312a5kbt(v=vs.84).aspx — Jiminy Cricket, Feb 28 '17 at 14:50
See [this answer](http://stackoverflow.com/a/20390880/4088852). — Comintern, Feb 28 '17 at 15:04
@Comintern thanks for your help there, I found it helpful however I am looking to see how to loop through different text files also within the same directory to add underneath the last import. If that makes sense? — MBrann, Feb 28 '17 at 16:24
You already have that part coded. The code from the linked answer goes inside your `Dir` loop. — Comintern, Feb 28 '17 at 16:27
@Comintern Sorry I know its been a long day so I am perhaps missing something simple here. I am entering the info in there and it is really not working at all. I am stepping into and it is not finding any errors at all, but only imports the first txt file and does not loop through the dir — MBrann, Feb 28 '17 at 16:47

score 2 · Answer 1 · answered Feb 28 '17 at 16:58

Just replace the Workbook code in your existing loop with code to read from a text file. Note that this is retrieving data row-wise, so the sample below just writes the entire line into column A. If the data needs further processing into columns, you can either do that inside the loop or pass the resulting string and row number to another Sub for processing.

Sub Theloopofloops()
    Dim Filename As String
    Dim path As String

    path = "L:\MK\Logger\"
    Filename = Dir(path & "*.xlsm")
    With ThisWorkbook.Sheets("Master")
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, 1).End(xlUp)
        Do While Len(Filename) > 0
            Dim handle As Integer
            handle = FreeFile
            Open path & Filename For Input As #handle
            Do Until EOF(handle)
                Line Input #handle, Data
                .Cells(lastRow, 1) = Data
                lastRow = lastRow + 1
            Loop
            Close #handle
            Filename = Dir
        Loop
    End With
End Sub

Looping through text files

1 Answers1