如何查阅在vba中之前打开的工作簿?

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.

我有一个微妙的问题使用VBA在MS Word。我试着参考一些书，这些书是在单词开始之前打开的。

From within a short test-macro in word a simple

从一个简短的测试-宏在word简单

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.

delievers的值为0，尽管已经打开了3个(空的)工作簿。当启动Word后打开3个工作簿时，我得到了3的正确值。

How to fix this ?

如何解决这个问题?

jm2p Zeph

2 个解决方案

#1

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

这是因为您必须获得正在运行的Excel实例，而不是创建一个新的实例

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:

下面的代码设置了一个Excel应用程序对象，试图首先获取任何正在运行的实例，然后，如果没有任何Excel会话正在运行，那么打开一个新的:

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

#2

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

#1