EXCEL ACCESS VBA data transfer

I am looking for any code example which will show me how to connect to an Access database from Excel and copy a field from an Access query and paste it into a field in an Excel worksheet
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
place this codes in your excel module and replace the variables to suit your requirements

Sub Open_Query()
    Dim db As Database, rs As Recordset, qd As QueryDef
    Dim ws As Object, i As Integer
    Dim dbPath As String
    dbPath = "<path to your access db>"
    Set db = OpenDatabase(dbPath, ReadOnly:=True)
    Set qd = db.QueryDefs("QueryName")
    Set rs = qd.OpenRecordset()
    Set ws = Sheets("sheet1")
        For i = 0 To rs.Fields.Count - 1
            ws.Cells(1, i + 1).Value = rs.Fields(i).Name

        ws.Range("A2").CopyFromRecordset rs

End Sub
IdaracAuthor Commented:
Yep works great just pasted in and it worked.
IdaracAuthor Commented:
Thank you for your quick response
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.