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

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobWpuYGyFeIWOm5ysrF2kvbW1zqdkraddp7KnvsSsn2aZXai2r7PLnmSqrZWnxm7B0qKloGWml64%3D