Hello,
I want to compare the load time of different queries with VBA (running several tests and writing down the refresh times).
These codes don't work, but they illustrate that ideally it would be without reference to sheet or ListObject:
ActiveSheet.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
____________
Dim ws As Worksheet
Dim lo As ListObject
Set ws = ActiveSheet
Set lo = ws.ListObjects(1)
lo.QueryTable.Refresh BackgroundQuery:=False
"BackgroundQuery:=False" is important for getting the timing right.
What code could I use? Thanks!
Hi Matthias,
If you just want to write the times:
Sub dd()
Dim StartTime As Date, StopTime As Date, Seconds As Double
Dim ws As Worksheet
Dim lo As ListObject
StartTime = Time()
Set ws = ActiveSheet
Set lo = ws.ListObjects(1)
lo.QueryTable.Refresh BackgroundQuery:=False
StopTime = Time()
Seconds = Round((StopTime - StartTime) * 24 * 60 * 60, 2)
Debug.Print StartTime, StopTime, Seconds
End Sub