FileSystemObject in VBA

Sometimes you want to treat files and folders as objects: test whether they exist, walk a folder's contents, read and write text. For that VBA offers the FileSystemObject (FSO), part of the Microsoft Scripting Runtime. Create it by late binding. You set no reference, and it's portable everywhere:

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Its everyday members:

• fso.FileExists(path) / fso.FolderExists(path) return True/False. Always test before you open.
• fso.GetFolder(path) returns a Folder object. Its .Files is the collection of files, which you walk with For Each f In fso.GetFolder(p).Files, and .SubFolders holds its child folders.
• f.Name, f.Path, f.Size, f.DateLastModified give you a file's metadata without opening it.
• fso.CreateTextFile(path, True) makes a text file, or overwrites one. fso.OpenTextFile(path, ForReading) opens one to read.

There's also the classic alternative, Dir. Long before FSO existed, VBA shipped it as a function that returns file names matching a pattern. Call Dir(pattern) once to prime it. After that, each call to Dir() with no argument gets the next match. When it returns "", the loop ends:

Dim fname As String
fname = Dir(folderPath & "\*.xlsx")      ' first match
Do While fname <> ""
    Debug.Print fname
    fname = Dir()                         ' next match
Loop

Dir is lighter, with no object to create, and it's the one you'll see in most consolidation macros. FSO is richer (metadata, recursion, text I/O) and reads more clearly. Know both. And file away one subtle Dir gotcha. Never call another Dir in the middle of the loop, say to check a different pattern, because there's only one Dir state and you'll corrupt the enumeration. If you need nested searches, use FSO or collect the names into an array first.

How do you read and write text files and build file paths?

You don't always need to open a CSV or a log as a workbook. Reading and writing it line by line as text is faster and simpler. In FSO style:

Dim ts As Object, line As String
Set ts = fso.OpenTextFile("C:\data\log.csv", 1)   ' 1 = ForReading
Do Until ts.AtEndOfStream
    line = ts.ReadLine
    ' Split(line, ",") gives the fields
Loop
ts.Close

To write a log line without opening Excel, open the file with Set ts = fso.OpenTextFile(path, 8, True) (8 = ForAppending, create if absent), then ts.WriteLine "2026-07-20,OK,11 files", then ts.Close. Always .Close, because an unclosed handle can lock the file.

Build paths by joining the folder and the file name with a single backslash and no assumptions. fso.BuildPath(folder, name) handles the separator for you. It saves you from the classic missing-slash bug, where C:\data + report.xlsx = C:\datareport.xlsx. Prefer relative anchors too. ThisWorkbook.Path gives the folder the tool itself is saved in, so a consolidator can look in ThisWorkbook.Path & "\branches" and keep working when the whole folder is moved or shared.