Cat On A Spreadsheet

Automating a Monthly Reporting Pipeline with VBA: From Folder Cleanup to Archiving

If you’ve ever spent hours every month tidying folders, copying files, running reports, and compressing them for delivery, you’ll love this post. In this case study, we’ll build a complete VBA-driven file automation workflow that:

Cleans up last month’s processed files
Gathers new source files into a workspace
Runs an automated process on them (you can plug in your own logic here)
Archives everything neatly with timestamped ZIPs
Sends a summary report

All in a single click.

Folder Structure Setup

Let’s assume this folder layout:

  C:\Reporting\
 
 ├── Source\
 
 ├── Working\
 
 ├── Processed\
 
 └── Archive\

Each month, new data files land in Source. We’ll process them into Working, output results to Processed, and zip them to Archive.

Step 1: Clean the Workspace

Before new processing starts, the script clears the Working and Processed folders so old files don’t interfere.

  Sub CleanFolders()
 
      Dim fso As Object, folder As Object, file As Object
 
      Set fso = CreateObject("Scripting.FileSystemObject")
 
      Dim foldersToClean As Variant, fPath As Variant
 
      foldersToClean = Array("C:\Reporting\Working\", "C:\Reporting\Processed\")
 
      For Each fPath In foldersToClean
 
          If fso.FolderExists(fPath) Then
 
              Set folder = fso.GetFolder(fPath)
 
              For Each file In folder.Files
 
                  file.Delete True
 
              Next file
 
          End If
 
      Next fPath
 
      MsgBox "Workspace cleaned!"
 
 End Sub

This keeps your workspace tidy and prevents duplicate runs.

Step 2: Move Source Files into Working Folder

Now, we copy the latest input files into the working area:

  Sub LoadSourceFiles()
 
      Dim fso As Object, src As Object, file As Object
 
      Set fso = CreateObject("Scripting.FileSystemObject")
 
      Set src = fso.GetFolder("C:\Reporting\Source\")
 
      For Each file In src.Files
 
          fso.CopyFile file.Path, "C:\Reporting\Working\" & file.Name, True
 
      Next file
 
      MsgBox "Source files loaded into Working folder."
 
 End Sub

You can modify this to filter only certain extensions — e.g., only .csv or .xlsx.

Step 3: Process the Files

This step depends on your workflow. As an example, we’ll read all .xlsx files in Working and copy the “Summary” sheet to a consolidated workbook.

  Sub ProcessFiles()
 
      Dim fso As Object, folder As Object, file As Object
 
      Dim wbSource As Workbook, wbDest As Workbook, ws As Worksheet
 
      Set fso = CreateObject("Scripting.FileSystemObject")
 
      Set folder = fso.GetFolder("C:\Reporting\Working\")
 
      Set wbDest = Workbooks.Add
 
      For Each file In folder.Files
 
          If LCase(fso.GetExtensionName(file.Name)) = "xlsx" Then
 
              Set wbSource = Workbooks.Open(file.Path)
 
              On Error Resume Next
 
              Set ws = wbSource.Sheets("Summary")
 
              On Error GoTo 0
 
              If Not ws Is Nothing Then
 
                  ws.Copy After:=wbDest.Sheets(wbDest.Sheets.Count)
 
              End If
 
              wbSource.Close False
 
          End If
 
      Next file
 
      wbDest.SaveAs "C:\Reporting\Processed\Monthly Summary.xlsx"
 
      wbDest.Close False
 
      MsgBox "Processing complete!"
 
 End Sub

This is your “core logic” section — it can be replaced with any kind of file processing.

Step 4: Archive the Results

Once the reports are generated, it’s time to zip and store them.

  Sub ArchiveResults()
 
      Dim sh As Object, zipFile As String, sourceFolder As String
 
      Set sh = CreateObject("Shell.Application")
 
      sourceFolder = "C:\Reporting\Processed\"
 
      zipFile = "C:\Reporting\Archive\Reports_" & Format(Now, "yyyymmdd_hhnnss") & ".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
 
      sh.NameSpace(zipFile).CopyHere sh.NameSpace(sourceFolder).Items
 
      Application.Wait (Now + TimeValue("0:00:03"))
 
      MsgBox "Results archived as: " & zipFile
 
 End Sub

Step 5: Send the Archive by Email

Finally, use Outlook automation to deliver the finished package:

  Sub EmailResults()
 
      Dim olApp As Object, mail As Object
 
      Dim zipFile As String
 
      zipFile = Dir$("C:\Reporting\Archive\", vbDirectory)
 
      Set olApp = CreateObject("Outlook.Application")
 
      Set mail = olApp.CreateItem(0)
 
      With mail
 
          .To = "finance@company.com"
 
          .Subject = "Monthly Reports - " & Format(Now, "mmmm yyyy")
 
          .Body = "The monthly reports have been processed and archived."
 
          .Attachments.Add "C:\Reporting\Archive\Reports_" & Format(Now, "yyyymmdd_hhnnss") & ".zip"
 
          .Send
 
      End With
 
      MsgBox "Report emailed successfully."
 
 End Sub

Step 6: Combine It All

You can now tie everything together into a single workflow:

  Sub RunMonthlyPipeline()
 
      Call CleanFolders
 
      Call LoadSourceFiles
 
      Call ProcessFiles
 
      Call ArchiveResults
 
      Call EmailResults
 
      MsgBox "Monthly reporting pipeline completed successfully."
 
 End Sub

Conclusion

This is a real-world automation pipeline that can be extended infinitely:

Add error handling and logging for robustness
Insert database exports, data cleansing, or Power Query refreshes in the process step
Schedule the pipeline using Windows Task Scheduler

With these building blocks, Excel VBA becomes a lightweight orchestration tool that automates entire reporting cycles — fully hands-free.