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

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?

CodePudding user response：

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].