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
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
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.... Else 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.