Today’s Office automation tutorial is focused on learning how to verify that a file exists in one of your computer directories.
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.
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.