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
Open a workbook, then start the Visual Basic Editor (Alt + F11) and copy the following macro into the editor
' returns TRUE if the workbook exists
blnWorkbookExists = False
If FullFileName <> "" Then blnWorkbookExists = Len(Dir(FullFileName)) > 0
Now wherever you need to check if a Workbook exists simply include the following code to your workbook
'Your code goes here....
MsgBox "The workbook does not exist"
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.