title: Using Access 2010 VBA list all open Excel Workbooks
body: <p>Ok, I must be missing something subtle here.</p>
  
  <p>In Excel 2010 this VBA works:</p>
  
  <pre><code>Private Sub ExcelTest()
  
  Dim wb As Workbook
  
  For Each wb In Workbooks
  
      Debug.Print wb.Name
  
  Next wb
  
  End Sub
  </code></pre>
  
  <p>I'm trying to replicate this in Access VBA and I've tried the following approaches in Access 2010 with no success.</p>
  
  <pre><code>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  '&lt;--- 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  '&lt;--- 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  '&lt;--- 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
  </code></pre>
  
  <p>I definitely have the "Microsoft Excel 14.0 Object Library" reference checked in Access.
  I'm just trying to list any open Excel Workbooks so that I can then act against that information.
  Thanks for your help in advance.</p>