Cat On A Spreadsheet

Advanced File System Operations with VBA: Managing Folders, Archives, and Bulk Files

While Excel VBA is most commonly associated with manipulating spreadsheets, it also has powerful capabilities for managing the Windows file system. By tapping into the FileSystemObject and other Windows APIs, you can automate tasks like:

Creating and deleting folders
Copying, renaming, and moving files in bulk
Searching through directories
Working with compressed (ZIP) archives

This post will show you how to leverage VBA for advanced file system automation that goes far beyond Excel.

Getting Started with FileSystemObject

The Microsoft Scripting Runtime (FileSystemObject) provides a clean way to interact with the file system. You can late-bind (no references needed) like this:

  Sub CreateFolderIfMissing()
 
      Dim fso As Object
 
      Set fso = CreateObject("Scripting.FileSystemObject")
 
      Dim folderPath As String
 
      folderPath = "C:\Reports\2025\"
 
      If Not fso.FolderExists(folderPath) Then
 
          fso.CreateFolder folderPath
 
          MsgBox "Created folder: " & folderPath
 
      End If
 
 End Sub

This checks whether a folder exists and creates it if necessary — perfect for report automation scripts.

Bulk File Operations

Suppose you want to move all CSV files from a “Downloads” folder to a “Processed” folder:

  Sub MoveCSVFiles()
 
      Dim fso As Object, f As Object, file As Object
 
      Set fso = CreateObject("Scripting.FileSystemObject")
 
      Set f = fso.GetFolder("C:\Users\Me\Downloads")
 
      Dim destPath As String
 
      destPath = "C:\Users\Me\Processed\"
 
      For Each file In f.Files
 
          If LCase(fso.GetExtensionName(file.Name)) = "csv" Then
 
              fso.MoveFile file.Path, destPath & file.Name
 
          End If
 
      Next file
 
 End Sub

This routine filters by extension and moves files accordingly. You could adapt it for copy, delete, or rename tasks.

Searching Files Recursively

Many times you need to search through subfolders. Here’s a recursive search that lists all Excel files under a given folder:

  Sub ListExcelFiles()
 
      Dim fso As Object, folder As Object
 
      Set fso = CreateObject("Scripting.FileSystemObject")
 
      Set folder = fso.GetFolder("C:\Projects")
 
      Call TraverseFolder(folder)
 
 End Sub
 
 Private Sub TraverseFolder(ByVal folder As Object)
 
      Dim file As Object, subFolder As Object
 
      For Each file In folder.Files
 
          If LCase(Right(file.Name, 5)) = ".xlsx" Then
 
              Debug.Print file.Path
 
          End If
 
      Next file
 
      For Each subFolder In folder.SubFolders
 
          Call TraverseFolder(subFolder)
 
      Next subFolder
 
 End Sub

This is excellent for log file processing, audits, or batch data collection.

Working with ZIP Archives

VBA can also manage ZIP files via the Windows Shell application. For example, compressing a folder:

  Sub ZipFolder()
 
      Dim sh As Object, zipFile As String, sourceFolder As String
 
      Set sh = CreateObject("Shell.Application")
 
      sourceFolder = "C:\Reports\2025\"
 
      zipFile = "C:\Reports\Archive.zip"
 
      ' Create empty ZIP if it doesn't exist
 
      If Dir(zipFile) = "" Then
 
          Open zipFile For Output As #1: Close #1
 
          Open zipFile For Binary As #1: Put #1, , Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0): Close #1
 
      End If
 
      sh.NameSpace(zipFile).CopyHere sh.NameSpace(sourceFolder).Items
 
      Application.Wait (Now + TimeValue("0:00:02")) ' Wait for copy
 
 End Sub

This code creates a valid empty ZIP if needed, then adds contents of a folder.

To extract files:

  Sub UnzipFile()
 
      Dim sh As Object
 
      Set sh = CreateObject("Shell.Application")
 
      sh.NameSpace("C:\Extracted").CopyHere sh.NameSpace("C:\Reports\Archive.zip").Items
 
 End Sub

Putting It All Together: Batch Archival

Here’s a real-world scenario: Move all CSVs to an archive folder and compress them.

  Sub ArchiveCSVs()
 
      Dim fso As Object, f As Object, file As Object
 
      Dim sh As Object, zipFile As String, destPath As String
 
      Set fso = CreateObject("Scripting.FileSystemObject")
 
      Set f = fso.GetFolder("C:\Users\Me\Downloads")
 
      destPath = "C:\Users\Me\Archive\"
 
      zipFile = destPath & "CSVs_" & Format(Now, "yyyymmdd_hhnnss") & ".zip"
 
      ' Create empty ZIP
 
      Open zipFile For Output As #1: Close #1
 
      Open zipFile For Binary As #1: Put #1, , Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0): Close #1
 
      Set sh = CreateObject("Shell.Application")
 
      For Each file In f.Files
 
          If LCase(fso.GetExtensionName(file.Name)) = "csv" Then
 
              sh.NameSpace(zipFile).CopyHere file.Path
 
          End If
 
      Next file
 
 End Sub

This gives you a timestamped ZIP archive of all CSVs in one shot.

Final Thoughts

Excel VBA can do much more than automate spreadsheets — it can serve as a lightweight automation hub for the file system. With a mix of FileSystemObject and Shell-based ZIP handling, you can:

Build automated backup routines
Manage report distribution pipelines
Archive old files automatically
Search and process logs across entire directories

Once you integrate these tools into your VBA toolkit, Excel becomes a full-fledged automation platform.