Member Avatar for Gobble45

Firstly, im not sure if this is the correct location for a VBA question?
Please move if necassary.

This is my first major project using VBA - Ive previosuly used vb.net quite alot, and still adjusting to understand both, and the differences.

My question is:
I have a folder with at times none, and at others up to 10-20 .txt files.
i want to loop through each file, and read each line from the file.
I have predefined variables that i want to asssign each of these lines (according to the order of lines read)
But im not sure how to read the file line by line.
I did a quick Google search and found that if use:

Open (*FILENAME*) For Input As File
While Not EOF(File)
Line Input #File, textofline

Then it would do what i wanted, but i cant specify the name of the file, and only that it can be named:
"Serial_*.txt"
The asterisk being any number greater than one (*sigh*)

Is there a way i can loop through the files in the designated folder. and read them one by one, after which i use the variables that are assigned by each line of the text file, to input into my spreadsheet.

SOrry if this is confusing, but im stuck :(

Regards
James

  • 1 Contributor
  • 1 Reply
  • 285 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by Gobble45

Dani AI

Generated

Two reliable approaches work well for this task: enumerate files with the FileSystemObject for robustness, or use VBA's built-in Dir for a compact solution. FileSystemObject (late binding with CreateObject or by adding the Microsoft Scripting Runtime reference) makes it easy to walk a folder, open each text file as a TextStream, read lines with ReadLine, and write parsed values into a worksheet. The short example below shows the common pattern and keeps file handling explicit so streams always get closed.

Sub ImportTextFiles()
    Dim fso As Object, fld As Object, fil As Object, ts As Object
    Dim folderPath As String, outRow As Long, line As String

    folderPath = "C:\Path\To\Folder"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(folderPath)
    outRow = 2

    For Each fil In fld.Files
        If LCase(fso.GetExtensionName(fil.Name)) = "txt" Then
            Set ts = fil.OpenAsTextStream(1, -2)  ' 1=ForReading, -2=UseSystemDefault
            Do While Not ts.AtEndOfStream
                line = ts.ReadLine
                ' parse line (Split/Instr) and map pieces into columns
                Worksheets("Sheet1").Cells(outRow, 1).Value = line
                outRow = outRow + 1
            Loop
            ts.Close
        End If
    Next fil
End Sub

Notes and common pitfalls: ensure streams are closed (ts.Close) so files are not locked; use Option Explicit and basic error handling when folders or files might be missing; trim and validate each line before mapping it to variables; if files contain different counts of lines or optional fields, normalize with Split checks. For non‑ANSI encodings (UTF‑8 with BOM, etc.) FSO can misread content — ADODB.Stream configured for UTF‑8 is a safer choice for that case.

Performance tip: collecting rows into a VBA array and writing the array to the sheet in a single Range.Value assignment is much faster than cell‑by‑cell writes for many lines. Since later reported the problem was solved, the above points cover typical edge cases that help compare or harden that working solution.

Member Avatar for Gobble45

I worked this out, if anyone would like to see the code, just let me know.

Regards
Gobble

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.