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:
...but it hangs / gets stuck
After a bit of messing around this seems to work for me:
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?
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
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
Does anyone know if there's a better way of doing this?