Use Excel VBA to check if workbook is opened

This Excel tutorial explains how to use Workbooks Open Method to open a closed workbook and check if workbook is opened.

Excel VBA Workbooks Open Method

In worksheet automation, we may need to programmatically open another workbook to change data based on the active workbook. Workbooks Open Method is very straight forward to use, you just need to specify the file name to open, but there are many other optional parameters you may want to use.

Note that Workbooks Open Method is different from Workbook Open Event, the latter is to trigger VBA when a workbook is opened.

Example of Excel VBA Workbooks Open Method

Assume that you have two workbooks. One is called FileA.xlsm, another is called FileB.xlsx

Now you want to open FileB from FileA, so FileA should contain the Macro (therefore .xlsm) and FileB does not need any Macro.

In FileA, create a Sub in Module and insert the below code, which will open FileB.

Workbooks.Open ("C:\Users\WYMAN\Desktop\folder\FileB.xlsx")

After you open FileB, you may need to change the data.  Therefore you need to give FileB a name in order to manipulate it.

The below code give FileB a name called masterWB.

Set masterWB = Workbooks.Open("C:\Users\WYMAN\Desktop\folder\FileB.xlsx")

If the FileB is already opened, you will receive an alert.

Now you can manipulate FileA (using thisworkbook) and FileB(using masterWB) from FileA

masterWB.Sheets("Sheet1").Range("A1").Value = "FileB"
ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "FileA"

Check if Workbook is opened already

You may already have FileB opened before the Macro is run, in that case you will receive an alert message if you use Workbooks.Open, this will prevent your Macro from further running.

There are some methods to test if a Workbook is already opened, below is my preferred method. This Function loop through  all currently opened Workbook to see if there is a Workbook name same as the name provided in the Function argument.

Public Function wIfWbOpen(wbName As String) As Boolean
    Dim oWB As Excel.Workbook
    wIfWbOpen = False
    For Each oWB In Application.Workbooks
        If oWB.Name = wbName Then
            wIfWbOpen = True
            Exit For
        End If
    Next
    Set oWB = Nothing
End Function

Finally use the Function in the Sub in which you want to run code in FileB.

Public Sub updateData()
    If wIfWbOpen("FileB.xlsx") Then
        Set masterWB = Workbooks("FileB.xlsx")
    Else
        Set masterWB = Workbooks.Open("C:\Users\WYMAN\Desktop\folder\FileB.xlsx")
    End If
End Sub

Outbound References

http://www.ozgrid.com/forum/showthread.php?t=63350