1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba refresh Power Queries in sequence

Discussion in 'Power Pivot, Power Map etc' started by k1s, Mar 15, 2017.

  1. k1s

    k1s Member

    Messages:
    71
    Hi

    I've got several power queries which update fine by themselves but cause excel to get stuck in a loop if I try to refresh multiple queries using VBA. I tried to get it to refresh sequentially like this:
    Code (vb):

    Public Sub RefreshPowerQuery()
    dim cn As WorkbookConnection

        For Each cn In ThisWorkbook.Connections
            If cn = "Query - Name_of_First_Query" Then cn.Refresh
            If cn = "Query - Name_of_Second_Query" Then cn.Refresh
        Next cn

    End Sub
    ...but it hangs / gets stuck

    After a bit of messing around this seems to work for me:

    Code (vb):

    Public Sub RefreshPowerQuery()
    dim cn As WorkbookConnection
     
        For Each cn In ThisWorkbook.Connections
            If cn = "Query - Name_of_First_Query" Then cn.Refresh
        Next cn
     
        For Each cn In ThisWorkbook.Connections
            If cn = "Query - Name_of_Second_Query" Then cn.Refresh
        Next cn

    End Sub
    I say "seems to work", because I'm not sure if it's actually waiting for one to finish before doing the next one or if it's just that by the time excel has gone through the For > Next list, the first query has finished anyay

    Does anyone know if there's a better way of doing this?
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    Not sure what you are trying to accomplish here. By default PowerQuery refresh isn't done simultaneously, but each refresh will be in succession.

    If your workbook has not just PowerQuery connection and you wish to refresh PowerQuery only... Since, PowerQuery doesn't have VBA object model... you'll need to check connection string for the provider.
  3. k1s

    k1s Member

    Messages:
    71
    I'm trying to avoid getting stuck at the blue spinning circle. If I manually refresh the queries one by one. Everything works fine. If I refresh them using vba one by one everything seems to be fine too. The problems happens when I try to refresh specific queries together using vba. The queries are from tables in the same workbook.

    I'm afraid I don't know what that means.
  4. k1s

    k1s Member

    Messages:
    71
    Are you sure about that?

    I have background refresh, unchecked/unticked for all my queries, yet if I
    I click on data > show queries then run the code below, I see the spinning wheel against the first query hasn't completed before it starts the next query, even if I put a message box in.

    Code (vb):
    Public Sub RefreshPowerQuery()
    dim cn As WorkbookConnection
        For Each cn In ThisWorkbook.Connections
            If cn = "Query - Name_of_First_Query" Then cn.Refresh
        Next cn

        MsgBox("Click OK to run second query")

        For Each cn In ThisWorkbook.Connections
            If cn = "Query - Name_of_Second_Query" Then cn.Refresh
        Next cn

    End Sub
    This sometimes causes the workbook to hang with the message:
    Only way to break at this point is to End Excel with the Task Manager and lose unsaved work.

    I suppose there might be a way to add a pause before each query refresh, but it would be arbitrary. I wouldn't know how long each query refresh would take exactly, so I'd have to add a long pause which would be annoying in use.

    Surely there must be a better way?
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    Yes, I'm reasonably sure.

    Loading circle means very little as 2nd query is already in queue while first one is running (but isn't refreshed until 1st is finished).

    Try this code.
    Code (vb):
    Sub Test()
    Dim con As WorkbookConnection

    For Each con In ThisWorkbook.Connections
        If InStr(1, con.Name, "Query -") Then
            With con.OLEDBConnection
                .BackgroundQuery = False
                .Refresh
            End With
        End If
    Next
    End Sub
    Thomas Kuriakose likes this.
  6. k1s

    k1s Member

    Messages:
    71
    Sure, but I would expect the first query circle to have finished spinning by the time the second starts spinning, wouldn't you?"

    About the code you added:

    With con.OLEDBConnection <--What does this do?

    .BackgroundQuery = False <-- is this necessary if Background refresh is already unticked for each query? Is this just double-checking?
  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
  8. k1s

    k1s Member

    Messages:
    71
    So indeed the first query might not have necessarily finished running before the second query starts? Then I'm back to the same problem?
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    Query and drawing is different. Data is already brought in, but not drawn.

    Test it and see if there's any issue. I've tested on my end by refreshing 5 separate query with 50k lines of data each. Had no issue.
  10. k1s

    k1s Member

    Messages:
    71
    I see. Well, then I guess the problem is that because successive queries refer to data drawn from earlier queries, it sometimes hangs/crashes because the earlier queries haven't finished drawing. Is there any way to test/trap if a query has finished drawing before refreshing the next query?
  11. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    Upload sample. Normally, you don't need to query drawn data.
    You can simply create reference or duplicate first query and build second query from there.
  12. k1s

    k1s Member

    Messages:
    71
    Unfortunately there's a whole bunch of sensitive data about pupils, etc. so posting a sample of the actual thing is not a good idea here. I'll try to recreate something non sensitive
  13. LeroyMPI

    LeroyMPI New Member

    Messages:
    1
    thanks that helped me :)

Share This Page