如何使用Excel VBA和ADO将查询结果放入数据表中？ [英] How to put query results into a datatable with Excel VBA and ADO?

问题描述

我想通过ODBC使用ADO从数据库表中提取记录并将其放在Excel工作表中。我可以做这个。最终，我希望数据包含在Excel表中。我知道如何通过选择适当的单元格并使用插入菜单来创建表格来手动执行此操作。如何在VBA代码中执行此操作，以将返回的查询结果放入Excel表中的目标工作表中？我尝试使用宏记录器，但是生成的代码没有帮助。

I want to use ADO via ODBC to pull records from a database table and put them in an Excel worksheet. I can do this. Ultimately, I want the data to be contained within an Excel Table. I know how to do this manually by selecting the appropriate cells and using the Insert menu to create the Table. How can I do this in my VBA code to have the returned query results be placed into the target worksheet in an Excel Table? I tried using the Macro recorder but the generated code was not helpful.

推荐答案

像这样的事情？

在导入数据后添加此代码。我假设以下。请相应地修改。

Add this code after you have imported the data. I am assuming the following. Please amend accordingly.

• 数据导入到Sheet1的单元格A1中

• The data is imported in Cell A1 of Sheet1

行1具有列标题

Sub Sample()

    Dim LastRow As Long, LastCol As Long
    Dim ws As Worksheet
    Dim rng As Range

    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    LastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

    Set rng = Range("$A$1:$" & Split(Cells(, LastCol).Address, "$")(1) & "$" & LastRow)

    With ws
        .ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1"
        .ListObjects("Table1").TableStyle = "TableStyleLight2"
    End With

End Sub

这篇关于如何使用Excel VBA和ADO将查询结果放入数据表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！