View the Time it Takes to Refresh All

[LW_Ambassador]

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

 

[Micron]

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.

 

[smozgur]

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.

Sub benchmark()
Dim start As Double
    start = Timer
    ActiveWorkbook.Connections(1).Refresh 
    ' ActiveWorkbook.RefreshAll
    Debug.Print Timer - start
End Sub

 

[LW_Ambassador]

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.

 

[LW_Ambassador]

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.

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!

 

[smozgur]

Thank you! I have added the VBA code and had success!

Glad to hear helps!

 

[Micron]

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

 

[LW_Ambassador]

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

 

[smozgur]

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