使用Access 2010 VBA列出所有打开的Excel工作簿

debugcn 发表于 Dev

埃斯特拉卡

好吧，我一定在这里缺少一些细微的东西。

在Excel 2010中，此VBA可以工作：

Private Sub ExcelTest()

Dim wb As Workbook

For Each wb In Workbooks

    Debug.Print wb.Name

Next wb

End Sub

我试图在Access VBA中复制它，并且在Access 2010中尝试了以下方法，但均未成功。

Private Sub AccessTest1()
'Derived from http://stackoverflow.com/questions/15958061/controlling-excel-
'workbook-from-access-2010-vba

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = New Excel.Application

For Each xlWB In Excel.Workbooks
    Debug.Print xlWB.Name
Next xlWB

'Nothing happens.
'Stepping through and hovering:
'xlApp = "MicroSoft Excel"
'xlWB = Nothing
'xlWB.Name = Object variable or With block variable not set.
'But doesn't throw an error.

End Sub

Private Sub AccessTest2()
'Derived from http://stackoverflow.com/questions/5729195/how-to-refer-to-excel-
'objects-in-access-vba

Dim xlApp As Excel.Application

Dim wb As Excel.Workbook

Set xlApp = New Excel.Application

For Each wb In Excel.Workbooks  '<--- Like this nothing happens
    Debug.Print wb.Name
Next wb

'Stepping through and hovering:
'xlApp = "MicroSoft Excel"
'wb = Nothing
'wb.Name = Object variable or With block variable not set.  But doesn't throw an error.

For Each wb In xlApp.Workbooks  '<--- This throws a "Object variable or 
                                 'With block variable not set" error
    Debug.Print wb.Name
Next wb

End Sub

Private Sub AccessTest3()
'Derived from http://stackoverflow.com/questions/5729195/how-to-refer-to-excel-
'objects-in-access-vba

Dim objExcelApp As Object
Dim wb As Object

Set objExcelApp = CreateObject("Excel.Application")

Set wb = objExcelApp.Workbook  '<--- Throws "Object doesn't support this property
                               'or method error"
'Hovering after error:
'objExcelApp = "MicroSoft Excel"
'wb = Nothing

For Each wb In objExcelApp.Workbooks
    Debug.Print wb.Name
Next wb

End Sub

我肯定在Access中检查了“ Microsoft Excel 14.0对象库”参考。我只是想列出所有打开的Excel工作簿，以便我可以对这些信息采取行动。感谢您的帮助。

博登

设置xlApp = New Excel.Application创建一个新的Excel实例-当然您那里没有任何工作簿。

您想要达到的目标是通过一个已经打开的Excel实例。因此，您必须使用getObject（）。

For语句中还有另一个错误...您必须使用xlApp.Workbooks而不是Excel.Workbooks。

以下代码应提供正确的结果：

Dim xlApp As Excel.Application
Set xlApp = GetObject(, "Excel.Application")

Dim xlWB As Excel.Workbook
For Each xlWB In xlApp.Workbooks
    Debug.Print xlWB.Name
Next xlWB

Set xlApp = Nothing
Set xlWB = Nothing

最后，请记住将对象变量设置为Nothing，以消除它们的困扰。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。