How to check a Workbook Exists


This macro will provide a quick and easy way to check that a Workbook exists before you attempt to access it.

This can be a problem when trying to open a Workbook from within another Workbook, or a Workbook that is required has been deleted or moved. If you try to access a Workbook that does not exist your macro will fail with the following debug message

Now to avoid this error include the following code into your workbook and call it before you begin any work on another workbook.

Check Workbook Exists

Step 1

Open a workbook, then start the Visual Basic Editor (Alt + F11) and copy the following macro into the editor

Function WorkbookExists(FullFileName As String) As Boolean
' returns TRUE if the workbook exists

blnWorkbookExists = False
If FullFileName <> "" Then blnWorkbookExists = Len(Dir(FullFileName)) > 0

End Function

Step 2

Now wherever you need to check if a Workbook exists simply include the following code to your workbook

If WorkbookExists(FullFileName) Then
'Your code goes here....
MsgBox "The workbook does not exist"
End If

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.