How to refer to workbooks opened before from within word vba?

Question

i've got a subtle problem using VBA on MS Word. I try to refer to some workbooks that were opened before word was started up.

From within a short test-macro in word a simple

MsgBox Workbooks.Count

delievers a value of 0 although 3 (empty) workbooks are opened. When the 3 Workbooks are opened after Word was started, i get the correct value of 3.

How to fix this ?

jm2p Zeph

You are talking about Excel rather than Word, right? I don't think that `Workbooks.Count`is available inside a Word macro. — SaschaM78, Sep 06 '16 at 10:14

score 1 · Accepted Answer · answered Sep 06 '16 at 10:05

it's because you must get the running instance of Excel instead of creating a new one

the following code set an Excel application object trying to get any running instance first and then, should no excel session be already running, open a new one:

Option Explicit

Sub LateBindingExcel()
    Dim xlApp As Object

    Set xlApp = GetExcelObject

    MsgBox xlApp.Workbooks.count
End Sub

Function GetExcelObject() As Object
    Dim excelApp As Object

    On Error Resume Next
    Set excelApp = GetObject(, "Excel.Application") '<--| try getting a running Excel application
    On Error GoTo 0
    If excelApp Is Nothing Then Set excelApp = CreateObject("Excel.Application") '<--| if no running instance of Excel has been found then open a new one

    Set GetExcelObject = excelApp '<--| return the set Excel application
End Function

score 0 · Answer 2 · answered Sep 06 '16 at 10:05

0

Check this out:

'Option Explicit

'Option Explicit

Sub check()

    Set objExcel = GetObject(, "Excel.Application")
    Set wbs = objExcel.Workbooks

    Debug.Print wbs.Count

    Set objExcel = Nothing
    Set wbs = Nothing

End Sub

answered Sep 06 '16 at 10:05

Vityata

42,633
8
55
100

You must comment the "Option Explicit". – Vityata Sep 06 '16 at 10:06
1

Ok, I have found my 3 Workbooks. But I have another Question now: Could there be more than one Instance of Excel open ? And how to enumerate them/find the right one ? – Javanaut Sep 06 '16 at 10:15
Check here, probably you will find something useful: http://stackoverflow.com/questions/30363748/having-multiple-excel-instances-launched-how-can-i-get-the-application-object-f – Vityata Sep 06 '16 at 15:06

How to refer to workbooks opened before from within word vba?

2 Answers2