Embed SQL Data into Excel Table

Question

I have an Oracle DB connection that pulls data from the DB into Excel. My only issue is that I want it pulled into a table rather than just dump the data into a sheet. My PivotTable needs to be refreshed based on the Table updating. This is my sample code that works. Is there a property for the QueryTables object that embeds the data into a table?

With ActiveSheet.QueryTables.Add(Connection:=Array(connection),Destination:=Range("A1"))

 .Sql = "SELECT * FROM TABLE"
 .FieldNames = True
 .Refresh BackgroundQuery:=False
End With

Parfait · Accepted Answer · 2015-10-10 21:25:05Z

1

Consider using a ListObject with the Query Table. Below is an example with a MySQL connection.

Dim constr As String

constr = "ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};server=localhost;_
          database=databasename;UID=username;PWD=password;"

With ActiveSheet.ListObjects.Add(SourceType:=0, _
          Source:=constr, _
          Destination:=Range("$A$1")).QueryTable

    .CommandText = "SELECT * FROM `Table`"
    .ListObject.DisplayName = "TableName"  
    .Refresh BackgroundQuery:=False
End With

answered Oct 10, 2015 at 21:25

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Embed SQL Data into Excel Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related