Tom’s Tutorials for Excel: How to verify if a workbook exists

Tom’s Tutorials for Excel: How to verify if a workbook exists.
Tom Urtis’ latest workbook tip.

When verifying if a certain workbook name exists, you would also want to verify if the source path and folder to which it supposedly belongs exists.

It is possible for the workbook to exist outside the source folder where the user thinks it should be, and it is possible the source folder path exists but the workbook is not actually on that computer.

As a service to your users, let them know if the path does not exist and what to do about it, and let them know if the workbook itself is not in the source folder.

To do that, two separate If structures evaluate the source folder path, and then the existence of the workbook in that folder. A Message Box informs the user if either the path or workbook are not found, and what steps they should take.

Sub VerifyFolderAndFile()

'Declare and define variables.
Dim SourcePathName As String
Dim SourceFileName As String
Dim SourceFullName As String
SourcePathName = "C:\Your\File\Path\"
SourceFileName = "YourWorkbookName.xls"
SourceFullName = SourcePathName & SourceFileName

'Verify existence of the source path.
If Len(Dir(SourcePathName, vbDirectory)) = 0 Then
MsgBox "There is no source folder named" & vbCrLf & _
"''" & SourcePathName & "''." & vbCrLf & vbCrLf & _
"Here's what you should do:" & vbCrLf & _
"(1) Click OK." & vbCrLf & _
"(2) Create a path named ''" & SourcePathName & "''." & vbCrLf & _
"(3) Come back here and try the macro again.", _
48, "Source path not found, cannot continue."
Exit Sub
End If

'Verify there really is a file named "YourWorkbookName.xls" in the path "C:\Your\File\Path\"
SourceFullName = SourcePathName & SourceFileName
If Len(Dir(SourceFullName, vbDirectory)) = 0 Then
MsgBox "There is no file named ''" & SourceFileName & "''" & vbCrLf & _
"in the path ''" & SourcePathName & "''." & vbCrLf & vbCrLf & _
"Please make sure there is that path established" & vbCrLf & _
"in your computer's directory, and that the workbook" & vbCrLf & _
"is housed in that path, spelled exactly as you see it" & vbCrLf & _
"in the quotes above.", 16, "Cannot continue -- source file not found."
Exit Sub
End If

'At this point, the source path and source file names are all found and the macro can continue.

'Advise the user:
MsgBox "The path and workbook names have been verified." & vbCrLf & _
"Click OK to proceed with the macro.", 64, "Looks good !!"
End Sub