📎 This article includes 2 downloadable practice files ↓

Most office automation starts with a folder: thirty branch files every month-end, a download folder full of bank statements, daily CSV exports from the ERP. This last lesson covers working with files and folders, then puts everything from the course into one project you can adapt for real work.

Looping through files with Dir

Sub ListFiles()
    Dim folder As String, f As String, r As Long
    folder = ThisWorkbook.Path & "\Branch files\"
    f = Dir(folder & "*.xlsx")                ' first match
    r = 2
    Do While f <> ""
        Worksheets("Files").Cells(r, 1).Value = f
        Worksheets("Files").Cells(r, 2).Value = FileDateTime(folder & f)
        Worksheets("Files").Cells(r, 3).Value = FileLen(folder & f)
        r = r + 1
        f = Dir()                              ' next match — no arguments
    Loop
End Sub

Dir with a pattern starts a search; Dir() with no arguments returns the next file; it returns an empty string when there are no more. Don’t call Dir for anything else inside the loop — it would restart the search.

Check, create and delete

If Dir(path, vbNormal) = "" Then MsgBox "File not found: " & path
If Dir(folder, vbDirectory) = "" Then MkDir folder
Kill folder & "*.tmp"                      ' permanent delete, no Recycle Bin!
FileCopy src, dst
Name oldPath As newPath                     ' rename or move

Reading a text or CSV file line by line

Dim fn As Integer, line As String, parts As Variant
fn = FreeFile
Open ThisWorkbook.Path & "\export.csv" For Input As #fn
Do While Not EOF(fn)
    Line Input #fn, line
    parts = Split(line, ",")
    ' parts(0), parts(1)… (simple CSVs only — commas inside quotes need a proper parser or Power Query)
Loop
Close #fn

Final project: combine every branch file

The brief: each month, 10–40 branch workbooks land in a folder. Each has a sheet called Sales with the same columns. Management wants one combined sheet with a Branch column, a summary by branch, and a dated copy saved in a Reports folder. Here’s how the course’s lessons fit together:

• Lesson 11 — open each file read-only, close it.
• Lesson 9 — read each sheet into an array, write back once.
• Lesson 14 — total by branch with a Dictionary.
• Lesson 10 — split it into small Subs and Functions.
• Lesson 7 — error handling so one broken file doesn’t stop the run.

Option Explicit

Sub BuildMonthlyReport()
    Dim folder As String, f As String, n As Long, skipped As String
    folder = PickFolder(): If folder = "" Then Exit Sub

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    PrepareOutput

    f = Dir(folder & "*.xls*")
    Do While f <> ""
        If AppendBranch(folder & f) Then n = n + 1 Else skipped = skipped & vbLf & f
        f = Dir()
    Loop

    BuildSummary
    SaveDatedCopy
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox n & " files combined." & IIf(Len(skipped), vbLf & "Skipped:" & skipped, "")
End Sub

Private Function AppendBranch(ByVal path As String) As Boolean
    Dim wb As Workbook, data As Variant, outWs As Worksheet, nextRow As Long, rows As Long
    On Error GoTo Fail
    Set wb = Workbooks.Open(path, ReadOnly:=True, UpdateLinks:=False)
    data = wb.Worksheets("Sales").Range("A1").CurrentRegion.Offset(1).Value
    rows = UBound(data, 1) - 1                       ' CurrentRegion.Offset adds one empty row
    wb.Close SaveChanges:=False

    Set outWs = ThisWorkbook.Worksheets("Combined")
    nextRow = outWs.Cells(outWs.Rows.Count, "A").End(xlUp).Row + 1
    outWs.Cells(nextRow, 1).Resize(rows, UBound(data, 2)).Value = data
    outWs.Cells(nextRow, UBound(data, 2) + 1).Resize(rows, 1).Value = _
        Replace(Mid$(path, InStrRev(path, "\") + 1), ".xlsx", "")
    AppendBranch = True
    Exit Function
Fail:
    If Not wb Is Nothing Then wb.Close SaveChanges:=False
    AppendBranch = False
End Function

PrepareOutput, BuildSummary (a Dictionary of totals by branch), SaveDatedCopy and PickFolder are in the practice workbook — about 60 lines in total. Read them, then change one thing: add a Month column, or skip files whose name starts with “~$” (Excel’s lock files).

Making it safe for real use

• Always open source files read-only and never save them.
• Restore ScreenUpdating and Calculation even after errors (an error handler at the top level).
• Report skipped files instead of failing silently — that list is often the most useful output.
• Keep a dated copy of each run with SaveCopyAs, so last month is never overwritten.

You’ve finished the course

Fifteen lessons ago you wrote your first variable. You can now read and write ranges, make decisions and loops, handle errors, build reusable functions, react to events, collect input with forms, and process whole folders of files quickly. That covers most of what office VBA is used for.

Good next steps: Power Query for repeatable data cleaning without code, Office Scripts if your files live in OneDrive, and the Excel function course to sharpen the formulas your macros write.