2

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.

Community
  • 1
  • 1
n8gard
  • 1,870
  • 8
  • 26
  • 41
  • Do you want the Table to be refreshable? What's the data source, brand and version, please? What version of Excel are you using? What actions did you record exactly? I've found that to be a very helpful way to get started with this kind of code. – Doug Glancy Feb 09 '12 at 03:43
  • Yes, I would like it to be refreshable but not necessarily updatable. I am using the PostgreSQL ODBC 9.0 driver. The actions I recorded were: selecting the data range, selecting Insert tab, clicking Table and giving it a name. I also left the box for headers checked. I do not see how to integrate the generated code with my data retrieval code. I am using Excel 2007. – n8gard Feb 09 '12 at 17:03

2 Answers2

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • This is perfect. Thank you so much. I added this sub to my module and tweaked it slightly to take the target worksheet as a param so now I can use it in multiple places. – n8gard Feb 10 '12 at 18:52
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.

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • Hi Doug. I'm looking to do this in my VB code when I pull the records from the database via ADO. I have been unable to find any code examples anywhere. – n8gard Feb 09 '12 at 23:53