View the Time it Takes to Refresh All

LW_Ambassador

New Member
Joined
Jun 11, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,
First post, so please bear with me. :)

I am using Excel 365. I have built a document pulling data from SQL and OneDrive. I've used Power Query and Power Pivot. I would like to make sure the document is performing at its best in the least amount of time possible. Is there a way I can see the total time it takes to refresh all data? I would like to tweak and make comparisons.

I don't know if it matters, but I am working in a server environment so it is definitely slower than working in a document off the server so if anyone has any helpful suggestions there, that would be great!

Thank you!
LW_Ambassador
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I don't know if you've provide enough info about what triggers the refresh, and in fact, if that's what is really happening. If it's an external app or connection, perhaps the trigger is the sheet(s) calculation or change event? Or maybe manually via the ribbon? Or maybe other code in the workbook, or ...?

If code was used to start the data exchange or refresh, that would be ideal because you could get the Time first then last and subtract last from first to get the elapsed time.
 
Upvote 0
Welcome to the MrExcel Message Board.

As @Micron mentioned, I would simply use a VBA macro for that. The following sample refreshes a connection (or all connections could be refreshed with RefreshAll method), and prints the time difference in the immediate window (or you can use MsgBox function). If you need this simple benchmarking, then it might help.
VBA Code:
Sub benchmark()
Dim start As Double
    start = Timer
    ActiveWorkbook.Connections(1).Refresh 
    ' ActiveWorkbook.RefreshAll
    Debug.Print Timer - start
End Sub
 
Upvote 0
Solution
I don't know if you've provide enough info about what triggers the refresh, and in fact, if that's what is really happening. If it's an external app or connection, perhaps the trigger is the sheet(s) calculation or change event? Or maybe manually via the ribbon? Or maybe other code in the workbook, or ...?

If code was used to start the data exchange or refresh, that would be ideal because you could get the Time first then last and subtract last from first to get the elapsed time.
I am currently doing a manual refresh all via the ribbon.
 
Upvote 0
Welcome to the MrExcel Message Board.

As @Micron mentioned, I would simply use a VBA macro for that. The following sample refreshes a connection (or all connections could be refreshed with RefreshAll method), and prints the time difference in the immediate window (or you can use MsgBox function). If you need this simple benchmarking, then it might help.
VBA Code:
Sub benchmark()
Dim start As Double
    start = Timer
    ActiveWorkbook.Connections(1).Refresh
    ' ActiveWorkbook.RefreshAll
    Debug.Print Timer - start
End Sub
Thank you! I have added the VBA code and had success!
 
Upvote 0
@smozgur; could you please explain how using the Ribbon to refresh calls that sub, because that is what the op's response suggests to me. To me, it looks like a sub that needs to be called somehow but not by the ribbon commands. I would have thought that to capture a ribbon command, you'd need a class module with an events handler, assuming that there is one for Refresh.
 
Upvote 0
@smozgur; could you please explain how using the Ribbon to refresh calls that sub, because that is what the op's response suggests to me. To me, it looks like a sub that needs to be called somehow but not by the ribbon commands. I would have thought that to capture a ribbon command, you'd need a class module with an events handler, assuming that there is one for Refresh.
Using @smozgur 's response I didn't use the refresh all on the ribbon. That's what I was using initially and just trying to time it myself.
 
Upvote 0
@smozgur; could you please explain how using the Ribbon to refresh calls that sub, because that is what the op's response suggests to me. To me, it looks like a sub that needs to be called somehow but not by the ribbon commands. I would have thought that to capture a ribbon command, you'd need a class module with an events handler, assuming that there is one for Refresh.
Right, that sub has nothing to do with the ribbon Refresh command because I didn't think it was necessary. I assumed that @LW_Ambassador just needed to measure the time that takes for a Refresh action (or RefreshAll) when I read the question. I think I made a correct assumption.
 
Upvote 0

Forum statistics

Threads
1,223,339
Messages
6,171,534
Members
452,409
Latest member
brychu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top