Excel VBA - Create table(ListObject) from existing query(Conenction Only)

Question

Using VBA in Excel I am trying to use a triggered event to create a new table with ListObjects.Add() from an existing query that has been created only as a connection and added to the data model, but must be getting the syntax wrong for the source.

I have a query called "DetailedProfit"

Created from Data > Get Data > From Other Sources > From ODBC
No DSN
Advanced options to specify connection string to IBM DB2 database using iSeries Access ODBC Driver and query
Used power query editor to transform data as well as some steps created with advanced editor
This works as intended
Loaded to "Only Create Connection" and added to data model

I have a triggering event which I would like to load the detailed profit connection into a newly created table, same as if I had selected Load to "Table" which runs this code

Set myTable = ActiveSheet.ListObjects.Add(SourceType:=xlSrcModel, Source:=ThisWorkbook.Connections("ThisWorkbookDataModel"), Destination:=Range("$A$1")).TableObject

I have tried chaning the source from a WorkbookConnection to WorkbookQuery as below as well

Set myTable = ActiveSheet.ListObjects.Add(SourceType:=xlSrcModel, Source:=ThisWorkbook.Queries("DetailedProfit"), Destination:=Range("$A$1")).TableObject

Both run into "Run-time error '5': Invalid procedure call or argument"

I believe I have the source wrong, so I have tried adding watches for both sources and crawling through properties, but I do not see anything that appears to be the data model connection to my query in the workbook connections.

Do I just have the syntax of how to refer to that connection wrong, or am I going about this completely wrong?

virpericulosus · Accepted Answer · 2023-01-18 16:13:47Z

0

Have you tried recording a macro see if that helps with syntax? Changing the Load To when I record gives:

ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DetailedProfit;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable

If you want to load data to VBA to do more work with it you can put Data Model data into a record set following this example https://stackoverflow.com/a/72122340/5830541. From there you could use Range.CopyFromRecordset.

From the linked answer I did have to change the query string to DAX syntax. That is, instead of SELECT:

    rs.Open "SELECT * From $" & TABLE_NAME & ".$" & TABLE_NAME, conn

I used "EVALUATE '" & TABLE_NAME & "'" for rs.Open [Source].

edited Jan 18, 2023 at 16:13

answered Jan 17, 2023 at 21:46

virpericulosus

616 bronze badges

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

1 Comment

Chris Over a year ago

Thank you, the advice to record a macro and look at the code was invaluable. I got something different than you, here is what I got, which works for me.

ActiveSheet.ListObjects.Add(SourceType:=xlSrcModel, Source:=ActiveWorkbook.Connections("Query - DetailedProfit"), Destination:=Range("$A$1")).TableObject

<br/><br/> So if you are looking to load an existing query that has been loaded to the data model the syntax appears to be Source:=ActiveWorkbook.Connections("Query - _QUERY_NAME_") You will then need to call the TableObject's .Refresh method to load populate the table.

Collectives™ on Stack Overflow

Excel VBA - Create table(ListObject) from existing query(Conenction Only)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related