Determine If A Workbook Is Open

You can determine if a workbook is currently open by viewing the Workbooks Collection, which contains all of the currently open workbooks in Excel. As a new workbook opens, it becomes a workbook object and Excel adds it to the Workbooks collection. Excel stores workbooks in the collection sequentially with the first workbook opened being the first workbook in the collection. If you know the order in which a workbook was opened, you can access it using the associated index value.

The code MyWorkbook = Workbook(1).Name uses the Name property to return the name of the first workbook in the collection to the MyWorkbook variable. The Name property, a read-only property, enables you to return the name of a workbook but prohibits you from changing the workbooks name. To change the name of the workbook, see the section "Save a Workbook."

In order to locate the workbook, you look at each workbook within the Workbooks Collection to determine if any of them is the workbook of interest. The For Each Next looping statement enables you to cycle through the list of workbooks and determine if the list contains the desired workbook. See Chapter 6 for more information about using a For Each Next looping statement.

Within the looping structure you need to compare the name of each workbook with the name of the desired workbook. For this type of VBA statement, you use an If Then statement, which enables you to check the value and execute a series of statements if the specified condition is True. See Chapter 6 for more information about using the If Then statement.

DETERMINE IF A WORKBOOK IS OPEN

DETERMINE IF A WORKBOOK IS OPEN

□ Create a new subroutine.

0 Type wbOpen = False.

□ Type For Each wb In Application.Workbooks.

□ Create a new subroutine.

—0 Type Dim wb As Workbook, replacing wb with the workbook variable.

0 Type Dim wbOpen As Boolean, replacing wbOpen with the variable to track if file is open.

—Q Type Dim wbFilename As String, replacing wbFilename with the string containing the name of the workbook to open.

0 Type wbOpen = False.

-□ Type wbFilename = "Budget.xls", replacing wbFilename with the workbook variable and "Budget.xls" with the name of the workbook to open.

□ Type For Each wb In Application.Workbooks.

0 0

Post a comment