Failed to solve Object required error running macro from excel into access and export to excel

[iP_123]

Hi.

I'm trying to write a macro in excel 2013 to copy an access query (this is just a run query that does not create a table) and export the results into an excel sheet. I have researched and tried several variations of codes seen online but I keep getting an "object required" error.


Any assistance will help.

      Sub excelvbatransferdatafromaccesstoexcel()
'Public Function MyFunction()
Dim A  As Object
Dim wkb As Object
Dim objSheet As Object
Dim xlApp As Object
Dim rng As Object
Dim strExcelFile As String
Dim iCol As Integer
'Dim db As DAO.Database


     Application.DisplayAlerts = False
      Set A = CreateObject("Access.Application")
      A.Visible = True
      A.OpenCurrentDatabase ("F:\Test Tables.accdb")
      A.DoCmd.OpenQuery "OpenTDb"
       
      Application.DisplayAlerts = True

WorkSheets("Sheet2").Activate
Range("A:F").Select
With Selection.ClearContents
End With

Set rst = A.DoCmd.OpenQuery("OpenTDb")
;'Set rst = db.OpenRecordset("OpenTDb")

For iCol = 1 To rst.Fields.Count
    WorkSheets("Sheet2").Cells(1, iCol) = rst.Fields(iCol - 1).Name
    Next iCol

WorkSheets("Sheet2").Range("A2").CopyFromRecordset rst
rst.Close
'Set db to Nothing

End Sub

Also when I Dim db as database I get a user-defined type not defined error not sure why. Lastly in the vba editor, tool and references when I check the MS DAO 3.6 Object Library box I get and error. If there is a way round to still be able to run a macro in excel to copy the query results from access into excel that will be great.


Thanks.