Refreshing queries in Excel using VBA

Question

long time reader, first time asker,

I have a master file that when i hit a button will Open 11 files one by one refresh the connections and close the files. however, it's not refreshing the connections before it closes the file.

Sub Task()

Workbooks.Open Filename:= "......sharepoint.com/sites/dummylocation/dummyfile.xlsx"

Dim WB As Workbook

Set WB = Application.Workbooks("dummy file.xlsx")
Dim ObjConnection As Variant
Dim bBackground As Variant

For Each ObjConnection In WB.Connections
'Get current background-refresh value
bBackground = ObjConnection.OLEDBConnection.BackgroundQuery
'Temporarily disable background-refresh
ObjConnection.OLEDBConnection.BackgroundQuery = False
'Refresh this connection
ObjConnection.Refresh
'Set background-refresh value back to original value
ObjConnection.OLEDBConnection.BackgroundQuery = bBackground
Next
WB.Close
End Sub

Ive been able to fault find it a little bit by commenting out the "wb.cose" which the Refresh then works. Im assuming i need some kind of delay or something?????

Perhaps `Application.Wait` as well as `DoEvents` https://learn.microsoft.com/en-us/office/vba/api/excel.application.wait — user10186832, Jan 16 '23 at 07:15

score 0 · Answer 1 · answered Jan 16 '23 at 08:09

I had a similar problem, and I got multiple solutions here

What worked perfectly for me was adding a function like this:

Function WaitUntilFinish(Seconds As Double)
    Dim StopTime As Double: StopTime = Timer + Seconds
    Do While Timer < StopTime
        DoEvents
    Loop
End Function

And then using it in the code. May I suggest you use it here?

'Refresh this connection
ObjConnection.Refresh
WaitUntilFinish (Seconds)
'Set background-refresh value back to original value
ObjConnection.OLEDBConnection.BackgroundQuery = bBackground

Refreshing queries in Excel using VBA

1 Answers1