This week I want to demonstrate how to verify whether or not a certain file (e.g. Docx, XLS, XLSM, etc.) exists within a certain directory on the computer.
Here is a macro that will scan drive C for a XLSM file named “ABC123” and will return "File Exists!" if that particular file exists and "File Does Not Exist in this directory" otherwise.
Dim ObjectFileName As String
Set CFS = CreateObject("Scripting.FileSystemObject")
ObjectFileName = "C:/ABC123.XLSM"
If Not CFS.FileExists(ObjectFileName) Then
MsgBox "File Does Not Exist in this directory"
MsgBox "File Exists!"
All you have to do is go to Developer> Visual Basic > Module. Then copy and paste the code above into the module page. Make sure to change the object file name on the fifth line of code to reflect the name and directory of the file you are searching for (i.e. “C:/ABC123.XLSM”).
Here is the message that I correctly got after running this macro. This shows that a file named “ABC123.XLSM” exists on my C drive.