none
Refresh Power Query via VBA RRS feed

  • Question

  • Attempting to refresh Power Query via VBA but receiving "Download did not complete." error on each query. Have tried various code approaches; here's the latest...

    For Each cn In TDR.Connections
    If Left(cn, 13) = "Power Query -" Then cn.EnableRefresh = True
    cn.Refresh
    Application.CalculateUntilAsyncQueriesDone
    Next cn

    Application.CalculateFullRebuild
    Application.CalculateUntilAsyncQueriesDone

    'Application.ScreenUpdating = True
    For Each cn In TDR.Connections
    If Left(cn, 13) = "Power Query -" Then cn.EnableRefresh = False
    Next cn

    The issue has been talked about before; specifically in the following threads without resolution. Was wondering if anyone had found a fix?

    https://social.technet.microsoft.com/Forums/en-US/b7cec158-b691-4174-b3d7-3f6a9f870d9f/power-query-refreshes-on-open-or-via-vba?forum=powerquery

    http://www.excelguru.ca/blog/2014/10/22/refresh-power-query-with-vba/

    Thursday, February 4, 2016 10:59 PM

Answers

  • Hello Tobias, the issue you are running into is a timing issue. Power Query updates the status of its connections  through polling, and likely the workbook is getting closed before the status update polling cycle is run.

    In your macro it appears that there should be plenty of time to update while the connections are being set back to disabled refresh, however that work is done on the UI thread and keeps the polling from running. 

    What works best is to set a callback timer using Application.OnTime so polling can work while the timer is waiting to callback.  Here is an example of code that works well.

    Sub RefreshPQConnectionsandClose()
        For Each cn In Application.ActiveWorkbook.Connections
         isPowerQueryConnection = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1") > 0
         If isPowerQueryConnection Then
            cn.OLEDBConnection.BackgroundQuery = False
            cn.Refresh
         End If
        Next cn
         Application.OnTime DateAdd("s", 10, Now), "CloseIt"
    End Sub
    
    Sub CloseIt()
         Application.ActiveWorkbook.Close (True)
    End Sub

    I am hoping you can modify this to make it work for your situation.

    Thanks,

    -jeff

    Tuesday, February 9, 2016 7:03 PM