Excel Workbooks Open Method and check if workbook open

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.

This tutorial will not focus on those optional parameters because they are rarely used, but I will copy the parameter description from MSDN for your references.

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

Syntax of Excel VBA Workbooks Open Method

The below contents are copied from MSDN

Filename	Required	Description
FileNname	Yes	The file name of the workbook to be opened.
UpdateLinks	Optional	Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 2, Microsoft Excel generates charts from the graphs attached to the file. If the argument is 0, no charts are created.
ReadOnly	Optional	True to open the workbook in read-only mode.
Format	Optional	If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used.
Password	Optional	A string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password.
WriteResPassword	Optional	A string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password.
IgnoreReadOnlyRecommended	Optional	True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).
Origin	Optional	If the file is a text file, this argument indicates where it originated (so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly). Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.
Delimiter	Optional	If the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use “,” for commas, use “;” for semicolons, or use a custom character. Only the first character of the string is used.
Editable	Optional	If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it’s a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option doesn’t apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, use True to open the specified template for editing or False to open a new workbook based on the specified template. The default value is False.
Notify	Optional	If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.
Converter	Optional	The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter doesn’t recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.
AddToMru	Optional	True to add this workbook to the list of recently used files. The default value is False.
Local	Optional	True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically U.S. English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).
CorruptLoad	Optional	Can be one of the following constants: xlNormalLoad, xlRepairFile, and xlExtractData. The default behavior if no value is specified is usually normal, but may be safe load or data recovery if Excel has already attempted to open the file. The first attempt is normal. If Excel stops operating while opening the file, the second attempt is safe load. If Excel again stops operating, the next attempt is data recovery.

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 already Opened

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

Excel Workbooks Open Method and check if workbook open