18 Dec 2016

Finding an open workbook

There are a few quick ways to check if a workbook is open when running a macro.

One method is by using error handling:

    Dim bCustom As Workbook
    On Error Resume Next
    Set bCustom = Workbooks("My Workbook 1.xlsx")
    
    If bCustom Is Nothing Then
        MsgBox "Could not find the workbook you are looking for.", vbCritical
        Exit Sub
    End If

    On Error Goto 0

This sets the workbook to the variable; if the variable is still set to nothing, then the workbook is not open.
Under normal circumstances, with no error handling, this code would stop running if the workbook is not open; the error handling means the error is suppressed and then an additional check is added to ensure that something was found.

However, a better method is to check every open workbook specifically – without making error handling necessary to the routine.

    Dim wb As Workbook
    Const MyWBName As String = "My Workbook 1.xlsx"
    
    For Each wb In Workbooks
        If wb.Name = MyWBName Then
            Set bCustom = wb
            Exit For
        End If
    Next

    If wb Is Nothing Then
        MsgBox "Could not find an open copy of " & MyWBName & "." & vbNewLine & vbNewLine & "Please open the file and try again.", vbCritical
        Exit Sub
    End If

It checks every workbook open in this instance of Excel (for up to versions 2010, for 2013, all open workbooks) and ensures the name matches that which we are expecting. Once it has found the workbook, it sets it to the variable and then jumps out of the loop.

Next, it checks the variable is set, if not then it provides a user prompt and ends the process.