How to put query results into a datatable with Exc

Question

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.

Answer 1

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.

Answer 2

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