+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    04-16-2020
    Location
    England
    MS-Off Ver
    Office 386
    Posts
    10

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

    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

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,637

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

    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.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    04-16-2020
    Location
    England
    MS-Off Ver
    Office 386
    Posts
    10

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

    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.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,508

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

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Power Query Refresh or Access Query - 2nd Query Run is faster
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2020, 10:16 AM
  2. Power Query shows as external data connection and can't refresh online
    By fuadramsey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-26-2018, 06:04 PM
  3. Power Query makes Excel crash - can't access Data Model
    By phil34 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2018, 01:01 PM
  4. Validate if value exist in current power query connection data
    By calvinle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2017, 01:03 AM
  5. Power Query from CSV - possible to move connection string to excel without MS PQ?
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-22-2016, 04:35 AM
  6. Power query load to connection
    By stephme55 in forum Excel General
    Replies: 1
    Last Post: 08-15-2016, 05:10 PM
  7. Connection Issues, Excel To Access Query, Access 2010
    By onechief in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-03-2015, 07:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1