Best Option to Refresh a Single Query Using Vba
I Have a Workboook with 4-6 Queries and I Just Would Like to Refresh 1 of the 6 with Vba. My Question Is, Am I Using the Fastest Option Below? and Nope, I...
I have a workboook with 4-6 queries and I just would like to refresh 1 of the 6 with VBA. My question is, am I using the fastest option below?
And nope, I don't want to use the Refresh All / Refresh button within Excel, I needed to include this in a sub.
CODE:
ThisWorkbook.Connections("Query - Raw").OLEDBConnection.refresh
' THESE ALSO WORK
' ActiveWorkbook.RefreshAll
' Selection.ListObject.QueryTable.refresh BackgroundQuery:=False
These don't work:
ActiveWorkbook.Connections("Raw").refresh
ThisWorkbook.Connections("Raw").refresh
Thank you for the kind answers in advance.
2 Answers
Fastest should be to refresh that specific query by name.
ThisWorkbook.Connections("YourOLEDBconnection").OLEDBConnection.refresh
This would be a smaller call stack but not much/if any of a noticeable time difference. It also only concerns itself with the connection open, refresh, close pathway.
Something like:
ThisWorkbook.Worksheets("SheetName").ListObjects("query table name").QueryTable.refresh BackgroundQuery:=False
I think would have a longer call stack making an additional call to the connection refresh shown at top. You may incur a small amount of overhead as well in relation to the table itself (any formatting that is re-painted etc).
You can time the various methods and look for the best median refresh time and take that method.
Connections has no exact name referred to query, this is the problem. I've solved it this way:
Sub refexample()
RefreshQuery "Query - Raw"
End Sub
Sub RefreshQuery(ByVal qry As String)
On Error Resume Next
Dim c As WorkbookConnection
For Each c In ActiveWorkbook.Connections
Debug.Print c.Name & " " & c.ranges(1).Parent.Name
If qry = c.ranges(1).Parent.Name Then
c.OLEDBConnection.Refresh
End If
Next
End Sub
Or if you renamed once all connections, you can use it directly:
Sub RenameQueries()
On Error Resume Next
Dim c As WorkbookConnection
For Each c In ActiveWorkbook.Connections
Debug.Print c.Name & " " & c.ranges(1).Parent.Name
c.Name = c.ranges(1).Parent.Name & "Conn"
Next
End Sub
Sub refexample()
ThisWorkbook.Connections("Query - RawConn").OLEDBConnection.Refresh
End Sub
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
