Files and folders: FileSystemObject and TextStream

Test and create paths, read and write text files, walk directories, and import CSV without corrupting the data.

FileSystemObject

Option Explicit

Private Function FSO() As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
End Function

Sub Paths()
    Dim f As Object
    Set f = FSO()

    Dim p As String
    p = f.BuildPath(ThisWorkbook.Path, "data")      ' correct separator per OS
    Debug.Print p
    Debug.Print f.GetParentFolderName(p)
    Debug.Print f.GetBaseName(p)                    ' data
    Debug.Print f.GetExtensionName("report.csv")    ' csv
    Debug.Print f.GetAbsolutePathName("..\logs")

    If Not f.FolderExists(p) Then f.CreateFolder p
    Debug.Print f.FileExists(f.BuildPath(p, "in.csv"))
    Debug.Print f.GetTempName()
End Sub

Sub ReadText()
    Dim f As Object, ts As Object
    Set f = FSO()

    Dim path As String
    path = f.BuildPath(ThisWorkbook.Path, "data\in.csv")
    If Not f.FileExists(path) Then Exit Sub

    Set ts = f.OpenTextFile(path, 1, False, -2)     ' 1 = ForReading, -2 = system default
    Dim line As String
    Dim n As Long
    Do Until ts.AtEndOfStream
        line = ts.ReadLine
        If Len(line) > 0 Then n = n + 1
    Loop
    ts.Close
    Debug.Print n & " non-empty lines"
End Sub

Sub WriteText()
    Dim f As Object, ts As Object
    Set f = FSO()
    Dim path As String
    path = f.BuildPath(ThisWorkbook.Path, "data\out.txt")

    Set ts = f.CreateTextFile(path, True, False)    ' overwrite, ANSI
    ts.WriteLine "header"
    ts.WriteLine "row 1"
    ts.Close

    ' append to the same file
    Set ts = f.OpenTextFile(path, 8, True, -2)      ' 8 = ForAppending
    ts.WriteLine "row 2"
    ts.Close
End Sub

• CreateObject returns late-bound objects: no IntelliSense, and every member call is resolved at run time, which is slower than early binding for a loop that reads a million lines.
• Always close a TextStream in a cleanup path. An unclosed handle keeps the file locked, and the next run fails with a permission error that looks unrelated.
• TristateUseDefault writes the system ANSI codepage, which breaks non-ASCII characters on another machine. Write UTF-8 explicitly when the consumer expects it.
• There is no UTF-8 option in FileSystemObject. For UTF-8 output, use ADODB.Stream with Charset = "utf-8", or a shell call.

Walking a directory

Function ListFiles(ByVal folderPath As String, ByVal pattern As String) As Collection
    Dim f As Object
    Set f = CreateObject("Scripting.FileSystemObject")

    Dim out As Collection
    Set out = New Collection

    If Not f.FolderExists(folderPath) Then
        Set ListFiles = out
        Exit Function
    End If

    Dim folder As Object, file As Object, sub_ As Object

    ' files in this folder only: * means all files
    For Each file In f.GetFolder(folderPath).Files
        If LCase$(Right$(file.Name, Len(pattern) + 1)) = "." & pattern Then
            out.Add file.Path
        End If
    Next file

    ' recurse into subfolders when you need the whole tree
    For Each sub_ In f.GetFolder(folderPath).SubFolders
        Dim nested As Collection
        Set nested = ListFiles(sub_.Path, pattern)
        Dim v As Variant
        For Each v In nested
            out.Add v
        Next v
    Next sub_

    Set ListFiles = out
End Function

Sub ImportAllCsv()
    Dim files As Collection
    Set files = ListFiles(ThisWorkbook.Path & "\data", "csv")

    Dim i As Long
    For i = 1 To files.Count
        Debug.Print files(i)
    Next i
End Sub

A recursive walk over a large directory tree is slow and can fail on a long path or a permission error mid-traversal. For a big tree, shell out to a short script or use Dir with a bounded recursion depth.

Importing CSV reliably

Sub ImportCsv(ByVal path As String, ByVal dest As Worksheet)
    Dim f As Object, ts As Object
    Set f = CreateObject("Scripting.FileSystemObject")

    If Not f.FileExists(path) Then
        Err.Raise vbObjectError + 1001, "ImportCsv", "file not found: " & path
    End If

    Dim lines As Collection
    Set lines = New Collection
    Set ts = f.OpenTextFile(path, 1, False, -2)
    Do Until ts.AtEndOfStream
        lines.Add ts.ReadLine
    Loop
    ts.Close

    If lines.Count = 0 Then Exit Sub

    ' split into a 2D array, then write once: never partly fill the sheet
    Dim header() As String
    header = Split(lines(1), ",")
    Dim cols As Long
    cols = UBound(header) + 1

    Dim data() As Variant
    ReDim data(1 To lines.Count - 1, 1 To cols)

    Dim i As Long, j As Long, fields() As String
    For i = 2 To lines.Count
        fields = Split(lines(i), ",")
        For j = 1 To cols
            If j - 1 <= UBound(fields) Then
                data(i - 1, j) = fields(j - 1)
            Else
                data(i - 1, j) = ""
            End If
        Next j
    Next i

    dest.Range("A1").Resize(UBound(data, 1), cols).Value2 = data
End Sub

' WARNING: Split on a comma is wrong for a real CSV file.
' A quoted field may contain a comma or a newline:
'   1,"Smith, John",42
' A correct parser tracks the quote state character by character.
' Where you have a choice, use Data > From Text/CSV, or an ADODB recordset,
' which handle quoting, codepages and type inference for you.

FAQ

Related

Error handling and the classic pitfalls Calling web APIs and parsing JSON in VBA

Last refreshed 2026-09-18.