Hi Blanka,

You should read this topic, it's a similar discussion.

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

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:

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.  

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 = False

    Connection.Refresh

Next Connection

Call CUSI
DoEvents

Call CUA
DoEvents

Call CUSH
DoEvents

End 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

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.

Many thanks, Catalin!

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?

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.

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.

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

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

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.