• 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...

  • 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

k1s

Member
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:
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:
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?
 

Chihiro

Excel Ninja
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.
 

k1s

Member
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.

Since, PowerQuery doesn't have VBA object model... you'll need to check connection string for the provider
I'm afraid I don't know what that means.
 

k1s

Member
...By default PowerQuery refresh isn't done simultaneously, but each refresh will be in succession...
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:
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:
"Attempted to read or write protected memory. This is often an indication that other memory is corrupt"
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?
 

Chihiro

Excel Ninja
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:
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
 

k1s

Member
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).
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?
 

k1s

Member
Nope. Depending on data size, drawing/populating data takes a bit more after query has finished.
So indeed the first query might not have necessarily finished running before the second query starts? Then I'm back to the same problem?
 

Chihiro

Excel Ninja
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.
 

k1s

Member
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?
 

Chihiro

Excel Ninja
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.
 

k1s

Member
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
 

LeroyMPI

New Member
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:
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
thanks that helped me :)
 
Top