CODEWITHSUNDEEP
vbaexcelms-access
Mr.Riply
Mr.Riply

Reputation: 845

running access query and copying the results into excel

I am fairly new at VBA, I apologize if this question is not up to the standards.

I am trying to run the Access query and copy the results to the Excel worksheet.

So far I have managed to open Access DB and run the query, but I can not find anything on how to copy the results into excel, at least I am not able to find a working solution.

Can someone please direct me in the right direction please.

Thank you.

So far I came up with the following code. 

Sub AccessTest1()

    Dim A As Object
    Application.DisplayAlerts = False
    Set A = CreateObject("Access.Application")

    A.Visible = True
    A.OpenCurrentDatabase ("acess database path")
    A.DoCmd.OpenQuery ("query")
    Application.DisplayAlerts = True


End Sub

Upvotes: 1

Views: 4323

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

Have a look at the CopyFromRecordset method.

Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range.

'...
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

Dim rs As Object
Set rs = A.CurrentDb().QueryDefs("QueryName").OpenRecordset()

If Not rs.EOF Then
    ws.Range("A1").CopyFromRecordset rs
End If

rs.Close

Upvotes: 4

Related Questions