Today’s Office automation tutorial is focused on learning how to verify that a file exists in one of your computer directories.
Preparation
If you are somewhat new to coding, ensure that the Microsoft Office Excel development tab is enabled and that you are familiar with some basic Excel VBA.
Checking if a file exists with Excel VBA
The following code leverages input boxes in order to capture the path to the file as well as the file name itself from the user.
Then it leverages the Dir method to verify if the file path indeed exists.
Lastly, it displays a message box to the user with the check result.
Sub Check_file_exists()
Dim NameDir As String
Dim NameFile As String
'Capture the file path and name from the users
NameDir = InputBox("Enter file directory")
NameFile = InputBox("Enter file name")
'Check whether the file exists
If (Dir(NameDir & NameFile)) = "" Then
MsgBox ("File doesn't exist")
Else
MsgBox ("File exists")
End If
End Sub
Check if a folder exists
In quite a similar fashion you can check whether a specific folder exists in your operating system. Also here we leverage the Dir method, but with the vbDirectory parameter.
Sub Check_folder_exists()
Dim NameDir As String
NameDir = InputBox("Enter file directory")
If (Dir(NameDir, vbDirectory)) = "" Then
MsgBox ("Folder doesn't exist")
Else
MsgBox ("Folder exists")
End If
End Sub
Using the Code
- Open Microsoft Excel and navigate to your workbook.
- Hit Developer and then hit Visual Basic.
- Copy and Paste the code from above as a subroutine in either a specific sheet of your VBA module.
- Run (F5) the code.
- Save your workbook.
Possible extensions
The boiler plate code above an be extended for the following use cases:
- Create a folder / file if it doesn’t already exist.
- Delete a file of folder provided they are present in your operating system file directory.
- Search for files/folders using a wildcard.