When saving files to a specific folder or reading files from a folder using VBA macros, if the target folder does not exist, an error will occur, and the macro will stop.
To prevent such errors, it is a golden rule to include a process that checks “Does the folder exist?” before execution and “If not, create it.”
Using the FileSystemObject (FSO), you can easily perform this sequence of checking and creating. This article explains the basic code pattern for this purpose.
Preparation: Reference Setting
To use FSO comfortably, it is recommended to open Tools > References in the VBA editor and check “Microsoft Scripting Runtime”.
Completed VBA Code
The following code checks if a subfolder with today’s date (e.g., Output_20250814) exists in the same location as the Excel file containing this macro. If it does not exist, it creates a new one.
' Reference: Microsoft Scripting Runtime
Sub EnsureFolderExists()
' Declare variables
Dim fso As New FileSystemObject
Dim targetFolderPath As String
Dim folderObj As Folder
'--- 1. Define the path of the folder to create ---
targetFolderPath = ThisWorkbook.Path & "\Output_" & Format(Date, "yyyymmdd")
'--- 2. Check for folder existence using the FolderExists method ---
If fso.FolderExists(targetFolderPath) = False Then
' If it does not exist
'--- 3a. Create a new folder using the CreateFolder method ---
Set folderObj = fso.CreateFolder(targetFolderPath)
MsgBox "Folder did not exist, so it was created." & vbCrLf & folderObj.Path
Else
' If it exists
'--- 3b. Get the existing folder object using the GetFolder method ---
Set folderObj = fso.GetFolder(targetFolderPath)
MsgBox "The specified folder already exists." & vbCrLf & folderObj.Path
End If
' Release objects
Set folderObj = Nothing
Set fso = Nothing
End Sub
Explanation of Code Points
1. Check for Folder Existence: .FolderExists
If fso.FolderExists(targetFolderPath) = False Then
The .FolderExists method of the fso object returns a Boolean value of True (exists) or False (does not exist) indicating whether the folder exists at the path specified in the argument. This If statement branches the processing depending on whether the folder exists.
2. Create a New Folder: .CreateFolder
Set folderObj = fso.CreateFolder(targetFolderPath)
If the folder does not exist, the .CreateFolder method is executed. This method creates a new folder at the specified path and returns the new Folder object as a return value.
3. Get Existing Folder: .GetFolder
Set folderObj = fso.GetFolder(targetFolderPath)
If the folder already exists, the .GetFolder method retrieves the object of that existing folder. This allows subsequent processing (such as saving files in that folder) to proceed smoothly.
Application: Making it a Practical Function
Since this “Check existence -> Create if missing” process is very common, it is very convenient to summarize it as a reusable Function like the one below.
' Function to ensure a folder is retrieved (created if missing)
Function GetOrCreateFolder(ByVal path As String) As Folder
Dim fso As New FileSystemObject
If Not fso.FolderExists(path) Then
fso.CreateFolder path
End If
Set GetOrCreateFolder = fso.GetFolder(path)
End Function
' How to use the function
Sub TestFunction()
Dim myFolder As Folder
Set myFolder = GetOrCreateFolder(ThisWorkbook.Path & "\MyReports")
MsgBox "Ready: " & myFolder.Path
End Sub
Summary
The basic pattern for preventing errors when handling folders in VBA is as follows:
If Not fso.FolderExists("Path") Then fso.CreateFolder "Path"
By simply adding this step at the beginning of a macro that outputs or reads files, you can avoid typical runtime errors stating “Path not found” and significantly improve the stability of your macros.
