运行访问查询并将结果复制到excel中

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

我在VBA相当新,如果这个问题不符合标准,我道歉。

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

我正在尝试运行Access查询并将结果复制到Excel工作表。

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.

到目前为止,我已经设法打开Access DB并运行查询,但我找不到任何关于如何将结果复制到excel的内容,至少我找不到可行的解决方案。

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

1 个解决方案

#1

Have a look at the CopyFromRecordset method.

看看CopyFromRecordset方法。

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

将ADO或DAO Recordset对象的内容复制到工作表上,从指定范围的左上角开始。

'...
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