title: Writing Excel VBA to receive data from Access
body: <p>I am writing an excel application that draws from an Access database for work. When the user opens the Excel tool, a data table needs to populate one of the worksheets from the Access database that I created. I have been writing the VBA code in excel and I am receiving Run-Time Error: "429" ActiveX Component Can't Create Object. </p>
  
  <p>The other questions are all written from Access but I believe I need this code written from the Excel workbook file. The code I have written is in the <code>Workbook_Open()</code> function so that the data is collected right as the user opens the file. Thanks very much for the help. BTW, I am using Access 2007 and Excel 2010.</p>
  
  <pre><code>Private Sub Workbook_Open()
      'Will fill the first listbox with data from the Access database
      Dim DBFullName As String
      Dim TableName As String
      Dim FieldName As String
      Dim TargetRande As String
  
      DBFullName = "D:\Tool_Database\Tool_Database.mdb"
  
      Dim db As DAO.Database, rs As Recordset
      Dim intColIndex As Integer
  
      Set TargetRange = Range("A1")
      Set db = OpenDatabase(DBFullName)
      Set rs = db.OpenRecordset("SELECT * FROM ToolNames WHERE Item = 'Tool'", dbReadOnly)
  
      ' Write the field names
      For intColIndex = 0 To rs.Fields.Count - 1
          TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
      Next
  
      ' Write recordset
      TargetRange.Offset(1, 0).CopyFromRecordset rs
  
      Set rs = Nothing
      db.Close
      Set db = Nothing
  End Sub
  </code></pre>