Log In
Register
Home
December 13, 2017
Offline
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!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
1 Guest(s)
