choosing specific Excel workbook is possible among multiple opened workbooks?

Question

Here is situation i faced. 3 workbooks has already been opened, each name of it is book1.xlsx, book2.xlsx, book3.xlsx

I tried to choose object of "book3.xlsx".

    Dim ExcelApp
    Dim objWorkBook, objSheets
    Dim args, param
    Dim result


    Set ExcelApp = GetObject(,"Excel.Application")
    Set objWorkBook = ExcelApp.ActiveWorkbook


    Wscript.Echo(objWorkBook.name)

the expected result was book3.xlsx but it shows book1.xlsx. choosing specific Excel workbook is possible among multiple opened workbooks?

Dave Dave · Accepted Answer · 2017-09-28T13:22:05

If you want to iterate through the different workbooks, use something like this:

Dim ExcelApp
Dim objWorkbook, objSheets
Dim args, param
Dim result

Set ExcelApp = GetObject(,"Excel.Application")
For Each objWorkbook in ExcelApp.Workbooks
    Wscript.Echo(objWorkBook.Name)
    If objWorkbook.Name = "book3.xlsx" Then
        Exit For ' This is the workbook we wanted
    End If
Next
If objWorkbook.Name = "book3.xlsx" Then
    'objWorkbook is set to book3, so do whatever you like
Else
    ' No book3 found, throw up a message box to warn the user
    MsgBox "No Book3.xlsx file found"
End If

If all you need is to select the workbook directly (and you're certain it'll exist):

Dim ExcelApp
Dim objWorkbook, objSheets
Dim args, param
Dim result

Set ExcelApp = GetObject(,"Excel.Application")
Set objWorkbook = ExcelApp.Workbooks("book3.xlsx")
'objWorkbook is set to book3, so do whatever you like with it

choosing specific Excel workbook is possible among multiple opened workbooks?

1 Answers