How to access data via VBA from a Connection Only Power Query query.

[MLAN_75]

Hello,

I have used Excel Power Query to create a Connection Only query which combines a number of tables within my Excel document. I do not want to return the results of this query back into Excel but rather I just want to access the results using VBA.

How do you make a connection to this Connection Only query and return the results?

I can find the connection using:

Please Login or Register  to view this content.

but do not know how to access the results of the query.

Any help is greatly appreciated.

Regards

[CK76]

Power Query it self does not return data, but only define connection and transformation step to bring in data.

It's probably easier to query using ADO code and load result to array.

Alternately, just load the PQ result to Data Model and use Pivot Table to perform analysis / calculation, rather than using VBA.

If you need further help. I'd recommend uploading desensitized sample file. Without it, it is difficult to help you.

[MLAN_75]

Thanks, I was not aware of what you have stated about Power Query. I have since worked around the issue with not being able to pull data from the Connection Only query. I have done this in the following way.

1) Create the Power Query query, merging the tables within my Excel sheet and performing transformations on the data to get the result set that I need. (this was and Id and value, but to get them I had data in 3 different tables)
2) Set the Power Query to return the data to a Table in a New EXCEL sheet.
3) Use VBA to access this ListObject and then loop through all of the rows executing my required code against each Id

The reason I need to do this was so that I could make multiple calls to a REST API to pull in additional information for each Id from the Query.

I have it all working now, I just didn't want to have to pull the Power Query data into the spreadsheet, it is just another sheet I will have to hide from the end users.

Thanks anyway for the response.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.