如何使用 Excel VBA 和 ADO 将查询结果放入数据表中？

How to put query results into a datatable with Excel VBA and ADO?

提问by n8gard

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.

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

采纳答案by Siddharth Rout

Something like this?

像这样的东西？

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

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

• The data is imported in Cell A1 of Sheet1

• Row 1 has column Headers

  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:$" & Split(Cells(, LastCol).Address, "$")(1) & "$" & LastRow)
  
      With ws
          .ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1"
          .ListObjects("Table1").TableStyle = "TableStyleLight2"
      End With
  
  End Sub
  

• 数据导入Sheet1的A1单元格

• 第 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:$" & Split(Cells(, LastCol).Address, "$")(1) & "$" & LastRow)
  
      With ws
          .ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1"
          .ListObjects("Table1").TableStyle = "TableStyleLight2"
      End With
  
  End Sub
  

回答by Doug Glancy

If you click the From Other Sources button on the Data tab you should see your ODBC listed. You can then specify the table to connect to. You will then have a refreshable Table that contains your data, in other words it combines what you're already doing with what you want to do into one step. Based on what you said in your comments I think this is the way to go, but let me know if I'm missing something.

如果单击“数据”选项卡上的“来自其他源”按钮，您应该会看到列出的 ODBC。然后，您可以指定要连接到的表。然后，您将拥有一个包含您的数据的可刷新表，换句话说，它将您已经在做的事情与您想要做的事情结合到一个步骤中。根据您在评论中所说的，我认为这是要走的路，但如果我遗漏了什么，请告诉我。