Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 37 ยท Files, Databases and APIs

File System Operations in VBA

Work with files and folders in VBA: Dir loops, FileSystemObject, copy, move, delete, create folders, build paths safely and let users pick files with dialogs.

Many macros have to find files, loop through a folder, copy or rename things, or ask the user where to save. VBA offers two toolkits for this: the old built-in functions (Dir, FileCopy, Kill, MkDir) and the Scripting.FileSystemObject (FSO) library. This lesson shows both, with safe ways to build paths and let users choose files.

VBA cannot run on this site, so the VBA is read-only. The runnable examples model the path-building and file-filtering logic in JavaScript, which is where most bugs hide.

Built-in functions

VB
If Dir("C:\Data\report.xlsx") <> "" Then Debug.Print "File exists"
If Dir("C:\Data", vbDirectory) <> "" Then Debug.Print "Folder exists"

MkDir "C:\Data\Archive"
FileCopy "C:\Data\report.xlsx", "C:\Data\Archive\report.xlsx"
Name "C:\Data\old.txt" As "C:\Data\new.txt"        ' rename or move
Kill "C:\Data\temp.txt"                             ' delete (no recycle bin!)
RmDir "C:\Data\EmptyFolder"                         ' only empty folders

Debug.Print FileLen("C:\Data\report.xlsx"), FileDateTime("C:\Data\report.xlsx")

Kill deletes permanently and accepts wildcards (Kill "C:\Temp\*.tmp"). Test carefully, and wrap risky calls in error handling (see error handling).

Looping a folder with Dir

VB
Sub ListExcelFiles()
    Dim folder As String, f As String
    folder = "C:\Data\"
    f = Dir(folder & "*.xlsx")
    Do While f <> ""
        Debug.Print f
        f = Dir                 ' next match
    Loop
End Sub

Dir with arguments starts a search; Dir with no arguments returns the next match; an empty string means the end. You cannot nest Dir loops, because there is only one search state, so collect names into an array or Collection first if you need more.

FileSystemObject

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

If fso.FolderExists("C:\Data") Then
    Dim file As Object
    For Each file In fso.GetFolder("C:\Data").Files
        Debug.Print file.Name, file.Size, file.DateLastModified
    Next file
End If

fso.CopyFile "C:\Data\a.txt", "C:\Backup\", True      ' True = overwrite
fso.MoveFile "C:\Data\a.txt", "C:\Archive\a.txt"
fso.DeleteFile "C:\Data\temp.txt", True
fso.CreateFolder "C:\Data\New"

Debug.Print fso.GetFileName("C:\Data\report.xlsx")        ' report.xlsx
Debug.Print fso.GetBaseName("C:\Data\report.xlsx")        ' report
Debug.Print fso.GetExtensionName("C:\Data\report.xlsx")   ' xlsx
Debug.Print fso.GetParentFolderName("C:\Data\report.xlsx")
Debug.Print fso.BuildPath("C:\Data", "report.xlsx")

FSO shines for recursion: GetFolder(...).SubFolders lets you walk a whole tree.

VB
Sub WalkFolder(fld As Object, depth As Long)
    Dim sub1 As Object, f As Object
    For Each f In fld.Files
        Debug.Print String(depth * 2, " ") & f.Name
    Next f
    For Each sub1 In fld.SubFolders
        Debug.Print String(depth * 2, " ") & "[" & sub1.Name & "]"
        WalkFolder sub1, depth + 1
    Next sub1
End Sub

FSO exists on Windows only. On a Mac use Dir, MacScript-free alternatives or Application.GetOpenFilename.

File and folder dialogs

VB
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .Title = "Choose a data file"
    .Filters.Clear
    .Filters.Add "Excel files", "*.xlsx;*.xlsm"
    .AllowMultiSelect = False
    If .Show = -1 Then
        Debug.Print .SelectedItems(1)
    Else
        Debug.Print "Cancelled"
    End If
End With

Dim picker As FileDialog
Set picker = Application.FileDialog(msoFileDialogFolderPicker)

Show returns -1 (True) if the user picks something, 0 if Cancel is pressed. There are also msoFileDialogSaveAs and msoFileDialogOpen types.

Paths: build them safely

  • Use ThisWorkbook.Path for files next to the macro file.
  • Join with fso.BuildPath or a small helper, so you never double or miss a backslash.
  • Environ("USERPROFILE") and Environ("TEMP") give per-user folders.
  • Cloud-synced folders (OneDrive) can make ThisWorkbook.Path return a URL starting with https://; test for that.
  • Avoid characters invalid in file names: \ / : * ? " < > |.

Try it: path and file-name helpers

JS BuildPath, GetBaseName and safe file names

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Dir-style loop with a wildcard

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Using Kill on a path built from user input without checking it first.
  • Nesting Dir loops; the inner call resets the outer one.
  • Forgetting that Excel creates ~$name.xlsx lock files for open workbooks when listing folders.
  • Hard-coding C:\Users\Me\... paths that fail on another computer.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Dir return when there are no more matching files?

    Dir returns an empty string when the search is finished or nothing matches.
  2. 2. Which tool is best for walking all subfolders recursively?

    FSO exposes Folder objects with Files and SubFolders collections that make recursion simple.
  3. 3. What does FileDialog.Show return if the user clicks Cancel?

    Show returns -1 when a selection was made and 0 when the dialog was cancelled.
  4. 4. Why is ThisWorkbook.Path useful?

    It lets your macro find files stored beside it without hard-coding a user folder.

Summary

  • Built-in Dir, MkDir, FileCopy, Name, Kill cover the basics; Kill is permanent.
  • Scripting.FileSystemObject adds objects, path helpers and recursion (Windows only).
  • Let users pick files with Application.FileDialog, and check the result of Show.
  • Build paths with helpers and base them on ThisWorkbook.Path rather than fixed user folders.

Next: Text and CSV Files in VBA