VBA Check whether a folder/directory exists (FolderExists)

VBA is very well suited for getting rid of certain annoying tasks under Microsoft Office and limiting one’s productivity to the essentials.

If you write a lot of VBA code, you will certainly need a function at some point to check whether a folder or directory path exists. It is actually part of good programming practice to carry out such a check. Because if you want to save something using VBA code, for example an e-mail or its attachments, you will get an exception message if the selected directory does not exist.

Testing if the folder exists is easily done with this function:

CreateObject("Scripting.FileSystemObject").FolderExists(strPath)

The CreateObject function returns an ActiveX object. The ActiceX object, in general terms, makes other operating system level functions available on Windows. Since the parameter “Scripting.FileSystemObject” is passed here, file and directory functions are made available in this case. In our use case we need the function FolderExists. As a transfer parameter, one specifies a string (strPath) with the complete directory path. The function then returns True or False, depending on whether the folder exists.

Table of content

Code example FolderExists

In this code example we work with variables for the folder path (strPath) and the boolean return value of the function (isDir), as the directory path will often be present as a string variable, for example as a passed parameter of a function. The function Debug.Print then writes the output to the direct window.

Dim strPath As String.
Dim isDir As Boolean
strPath = "c:\Users\MyUserName\Desktop\"
isDir = CreateObject("Scripting.FileSystemObject").FolderExists(strPath)

Debug.Print isDir

Instead of Debug.Print, one will of course process the result isDir and check with an If statement whether the directory check is True or False. If the directory does not exist yet, you could create the directory with the MkDir(…) command in another statement.

Dim strPath As String.
Dim isDir As Boolean
strPath = "c:\Users\MyUserName\Desktop\"
isDir = CreateObject("Scripting.FileSystemObject").FolderExists(strPath)

If isDir = True Then.
  'Do something, for example save a file under the directory.
  Debug.Print "Directory exists".
Else
  'Do something if folder does not exist.
  Debug.Print "Directory does not exist"
End If

For those lazy about writing, here’s another short variant:

If CreateObject("Scripting.FileSystemObject").FolderExists("c:\Users\MyUserName\Desktop\") Then.
  'Do something, for example save a file under the directory.
  Debug.Print "Directory exists".
Else
  'Do something if folder does not exist.
  Debug.Print "Folder does not exist"
End If

Code example Dir

Another method, which I don’t find as elegant, but which works just as well, is shown in the following code example. Here, the function Dir(…) is used, which returns an empty string if the folder does not exist.

 'Define string for folder path.
  Dim strFolderPath As String.
  strFolderPath = "c:\Users\MyUserName\Desktop\1\"
  ' Check if folder already exists.
  If Dir(strFolderPath, vbDirectory) = "" Then.
    ' Folder does not exist yet.
    Debug.Print "Folder does not exist".
  Else
    ' Folder exists.
    Debug.Print "Folder exists!"
  End If

As parameters, you have to pass the directory path (strFolderPath) to the Dir function as well as the attribute vbDirectory.

Conclusion

So you see, it’s relatively easy in VBA to check if a folder exists. And you should make frequent use of it, especially if it is uncertain whether the directory has already been created or the folder path has been specified by user input.

Bestseller No. 1