none
Excel 2016 - Can you disable power query with VBA RRS feed

  • Question

  • I have many power queries pulling data into a spreadsheet. I have been trying to refresh queries one at a time using various methods to no avail. To make matters worse the queries constantly show that they are refreshing when the query list is displayed. All queries and data connections have backgroundquery set to false; enablerefresh set to false; maintain connection set to false. etc. I even loop through all connections to cancel them at the end of the macro to update the queries:

    For Each con In ActiveWorkbook.Connections
    On Error Resume Next
    con.ODBCConnection.CancelRefresh
    con.OLEDBConnection.CancelRefresh
    con.ODBCConnection.EnableRefresh = False
    con.OLEDBConnection.EnableRefresh = False
    On Error GoTo 0
    DoEvents

    I also have Application.CalculateUntilAsyncQueriesDone and DoEvents throughout the macro in an attempt to make queries stop. Unfortunately I wasted a lot of time setting up power queries under the assumption it would be more reliable than pulling data using SQL in VBA. So I'd like to make this work so I can update queries and be 100% sure that all queries have updated completely and the data I am using is accurate. If a query table is showing that it is refreshing (even though its background query setting is false and I just cycled through all connections with the cancel command above) then I cannot be sure data is complete and accurate.

    I was wondering if it was possible to disable power query in Excel 2016. I was going to try to:

    1. Disable PQ so I have a starting point
    2. Enable PQ
    3. Refresh one data connection/query
    4. Once refresh is complete, disable PQ and make sure that no queries are refreshing
    5. Repeat for each query.

    It's kind of a silly process but it's that or start over using SQL. Appreciate any thoughts or recommendations.

     
    Tuesday, October 8, 2019 10:25 PM

Answers

  • Hi there,

    I am wondering if you have a sample workbook that demonstrates the problem? It will help us to investigate. Just make sure the workbook doesn't contain any private or sensitive information before sharing.


    Guy Hunkin - Excel Team

    Monday, October 28, 2019 5:55 PM