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

Question

好的，我必须在这里略微遗漏一些东西。

在Excel 2010中这个VBA工程：

Private Sub ExcelTest() 

Dim wb As Workbook 

For Each wb In Workbooks 

    Debug.Print wb.Name 

Next wb 

End Sub 

我想在访问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 Object Library”引用。 我只是想列出所有打开的Excel工作簿，以便我可以针对这些信息采取行动。 感谢您的帮助提前。

Answer 1

设置xlApp =新建Excel.Application创建Excel的新实例 - 当然，您还没有任何工作簿。

你想要存档的是通过一个已经打开的Excel实例。因此你必须使用getObject（）。

另一个错误是在您的For语句...您必须使用xlApp.Workbooks，而不是Excel.Workbooks。

下面的代码应提供exprected结果：

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 

也请记住将它们设置为没什么可destry在结束对象变量。