Open Workbook Safely Excel VBA Macro

Excel VBA generates and error if the file we try to open does not exist. It also display an alert and breaks the code if the file is already open. This macro checks for those two conditions before actually opening the file.

Macro/VBA code:

  Sub OpenWorkbookSafely()

      Dim wbPath As String, wbName As String, wbFile As String

      Dim wb As Workbook

      wbPath = ThisWorkbook.Path

      wbName = "TestFile.xlsx"

      wbFile = wbPath & "\" & wbName

      If Dir(wbFile) = "" Then

          MsgBox "File does not exist"

      Else

          For Each wb In Workbooks

              If wb.Name = wbName Then

                  MsgBox "File is already open"

                  Exit Sub

              End If

          Next

          Workbooks.Open wbFile

      End If

  End Sub

Macro explained:

• We declare three string variables for path, name and whole file destination, and a Workbook object (wb).
• We define wbPath as the same drive path where the workbook with this macro is located. This could be changed to any other location as follows: wbPath="C:\Users\Username\Documents”
• We also define wbName and the final wbFile as the combination of drive path and file name (including the extension).
• Then we check if the file exists with the Dir function. If it doesn’t exist Dir(wbFile) equals “”, we display a message box alert.
• Otherwise (Else), we loop through all open workbooks with a For Each loop to check if our file is already open. If the names match it means is open and we display the message and exit the procedure with Exit Sub.
• If nothing happens in the For Each loop it means the file was not open and then we can safely open the file with: Workbooks.Open wbFile

This is how we safely open a workbook in Excel with VBA macros.

Other examples:

• How to Add Button To Fit Selection with Excel VBA macros 
• How to List and Link All Sheets with Excel VBA macros
• How to List Unique Entries with Excel VBA macros