Log In
Register
Home
October 17, 2015
Offline
Hi All,
I have three tables created by Power Query in my file.
Is there a way to have a macro refresh the queries, ideally one at a time? And the macro would need to pause performing other tasks until the queries are finished refreshing.
I found something on the web, but it pertained to pivot tables and I am not using them in my file.
Thank you,
Blanka
See Answer
November 8, 2013
Offline
Hi Blanka,
You should read this topic, it's a similar discussion.
October 17, 2015
Offline
Hi Catalin,
Thank you for responding.
I clicked on the link you provided, but it comes up empty. I wonder if it's part of the forums reserved to people who are currently subscribed to Power Query training. My subscription already expired.
I would be very grateful for any help.
Thank you,
Blanka
November 8, 2013
Offline
Indeed, it's in the Power Quwry forum for members.
You have 2 options to refresh a connection: with background refresh set to false or to true.
Go to Data-Connections, select the connection you want, click on Properties, and uncheck the checkbox "Enable background refresh"
This way, the next line will not be executed until the refresh is complete.
If you have many connections, use a simple code to disable that option:
Dim Connection As Variant
For Each Connection In ActiveWorkbook.Connections
Connection.OLEDBConnection.BackgroundQuery = False
Next Connection
End Sub
You will get such code if you use the macro recorder while performing this action manually for a connection. Unfortunately, there is a bug, the code will continue before the last query is completed, here is Gregory's workaround:
Gregory Regan said
I have the solution! Crude but should work...When refreshing the queries programmatically, they seem to process in alphanumeric order based on the name of the queries. Therefore, create a permanent hidden dummy WorkSheet with a single row of data and then create a simple query on it and name the query "ZZZZZZZZZZZ_DummyQry" or something similar.
When the code loops through the queries, the "ZZZZZZZZZZZ_DummyQry" query will run last (and finish in a fraction of a second) ensuring that last genuine query is finished processing before moving onto the code to protect all worksheets because of the code line Connection.OLEDBConnection.BackgroundQuery = False.
Answers Post
October 17, 2015
Offline
Thank you so much, Catalin.
To double check - I created a dummy ZZZZ query and used your code and entered the command to refresh all my connections in the following place:
Sub Convert()
Dim Connection As Variant
For Each Connection In ActiveWorkbook.Connections
Connection.OLEDBConnection.BackgroundQuery = FalseConnection.Refresh
Next Connection
Call CUSI
DoEventsCall CUA
DoEventsCall CUSH
DoEventsEnd Sub
Is this correct?
I'll have to do some testing after the weekend, but I think it might work how I need it.
Thank you again.
Blanka
November 8, 2013
Offline
Yes, should work.
Keep in mind that you don't have to set the BackgroundQuery to false in this code, it's enough f you do it manually, or just run it once to change that setting.
October 17, 2015
Offline
Many thanks, Catalin!
New Member
October 26, 2017
Offline
It's not working, i had just copy paste in VBA, still it's not working, can you please let me know how to do?
November 8, 2013
Offline
Hi Darshan,
You will have to provide more details, screenshots there is no clue about what you are doing and what does not work.
Thank you for understanding.
It's better if you open your own topic instead of writing in an older topic.
New Member
January 13, 2019
Offline
Catalin Bombea said
You will get such code if you use the macro recorder while performing this action manually for a connection. Unfortunately, there is a bug, the code will continue before the last query is completed
Just hit this issue myself.
If the bug is still an issue, another (untested) workaround is to loop twice.
This may still be an issue with a single connection.
Sub ChangeConnectionBackgroundRefreshMode() 'On Error Resume Next Dim conn As WorkbookConnection Dim bugfix As Integer For bugfix = 1 To 2 For Each conn In ActiveWorkbook.Connections With conn If (.Type = xlConnectionTypeOLEDB) Then .OLEDBConnection.BackgroundQuery = false Else If (.Type = xlConnectionTypeODBC) Then .ODBCConnection.BackgroundQuery = false End If End If End With Next conn Next bugfix End Sub
Thanks,
Andrew K.
Active Member
October 11, 2019
Offline
I cannot get the solutions above to work for me. I have created a simple set of 3 queries in the workbook, the first two take about 5 seconds to refresh, the third is one row query called ZZZZ. The code below still jumps to the MsgBox line.
Any suggestions much appreciated. I need to run a protect wb code in my real work situation but the macro falls over because the Query is still refreshing and trying to load up the table after the wb is locked again.
Option Explicit
Sub refreshQ()
Dim conn As WorkbookConnection
Dim bugfix As Integer
For bugfix = 1 To 2
For Each conn In ActiveWorkbook.Connections
With conn
If (.Type = xlConnectionTypeOLEDB) Then
.OLEDBConnection.BackgroundQuery = False
Else
If (.Type = xlConnectionTypeODBC) Then
.ODBCConnection.BackgroundQuery = False
End If
End If
End With
Next conn
Next bugfix
For Each conn In ActiveWorkbook.Connections
conn.Refresh
Next conn
MsgBox "Hello"
End Sub
New Member
December 9, 2020
Offline
Why is this still a problem 3 years later! Shesh Microsoft! Anyway, THANKS to Gregory.
Step 1: I created a "hello world" query from a blank query and a sheet to store result
Step 2: Refresh query before my other queries seems to do the trick. (BTW, I have a trick to pull in only "pages" from PDFs if anyone interested).
' ONLY RUNNING THIS TO PREVENT "UNEXPECTED ERROR" Silly frown POPUP ' It forces Power Query to finish up last query before VBA code asks to do another in a loop
Sheets("DummyQueryForKludgeWait").Select Columns("A:A").
Select Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Started getting problems with above, so doing this now
' ONLY RUNNING THIS TO PREVENT UNEXPECTED ERROR POPUP
' it forces Power Query to finish up before VBA code asks for more
Sheets("DummyQueryForKludgeWait").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DummyQueryForKludgeWait;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [DummyQueryForKludgeWait]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "DummyQueryForKludgeWait"
.Refresh BackgroundQuery:=False
End With
' Now delete link to query, I wish I understood better!
Columns("A:A").Select
Selection.ListObject.QueryTable.Delete
Selection.ClearContents
Range("A1").Select
New Member
August 30, 2022
Offline
I also set background refresh to off, then I use the following code to specify the order of refresh:rnDim wb as workbookrnSet wb = ThisWorkbookrnwb.Connections("Query - [your query name here]").RefreshrnI still had some problems with queries not seeming to be finished before others started, so I added a 1 second delay in between using a sub I call "wait"rnSub Wait()rnDim PauseTime, Start, Finish, TotalTimePauseTime = 1 ' Set duration.Start = Timer ' Set start time.Do While Timer < Start + PauseTimeDoEvents ' Yield to other processes.LoopFinish = Timer ' Set end time.TotalTime = Finish - Start ' Calculate total time.rnEnd Subrn rnI found all these answers on the web, but I don't remember where I found it to give proper credit. I wanted to pass along the solution, though.
1 Guest(s)
