Display date when data last refreshed from external data

[pdqexcel]

I have a pivot table that is loaded from an external data source. The data source returns about 20 mg of data so the load takes about 2 minutes.

I do not want to set the table options to 'Refresh on open' because users may open the workbook and be confused because, during the data load, the workbook appears to be frozen (yes I know the status bar displays the records being loaded, but the users don't see this).

Is there any way to display a date when the workbook was last refreshed?

This date would be controlled by the 'Refresh Data' function for pivot tables.

 

[pdqexcel]

It would appear that my post was not challenging enough for the masters prowling this board, so after burnig a number of hours I figured out the answer myself. By the way, I have never ventured out into the Excel VBA editor before so this was a hugh personal success.

1) Right-click the tab name and select view code.
2) Select Worksheet in the upper left dropdown box.
3) Select PivotTableUpdate in the upper right dropdown box.
4) Insert the following code between the Private... and End Sub
Range("A2").Value = Now()
5) "A2" is the destination cell for the date(change as required).
6) Use Format Cell/Number/Date to select desired output format of date
7) Use Tools/Options/Security/Macro Security and set to Medium (if security is set to High, then the code will never execute.

 

[Charlie_Days]

Hi pdqexcel,

Firstly, thank you so much for the guidelines above, it helped me a lot while managing Pivot-Tables, but it doesn't seem to work with a Table with external data... If I try code above, i can even add MsgBox and it works.. but just for pivot-tables, doing the same for tables with TableUpdate it doesn't do anything, would you know if I am doing something wrong here? Thank you!

 

[grahamg]

I know this is an old post, but...This worked great for me. Thanks!

It would appear that my post was not challenging enough for the masters prowling this board, so after burnig a number of hours I figured out the answer myself. By the way, I have never ventured out into the Excel VBA editor before so this was a hugh personal success.

1) Right-click the tab name and select view code.
2) Select Worksheet in the upper left dropdown box.
3) Select PivotTableUpdate in the upper right dropdown box.
4) Insert the following code between the Private... and End Sub
Range("A2").Value = Now()
5) "A2" is the destination cell for the date(change as required).
6) Use Format Cell/Number/Date to select desired output format of date
7) Use Tools/Options/Security/Macro Security and set to Medium (if security is set to High, then the code will never execute.[/QUOT

 

[myerz]

Yep worked great for me too. This is very useful information.

 

[PDXTony]

Hello from 2019 - your post from 15 years ago has saved my bacon today!

Works like a charm.

 

[jlw125]

Another 2019er with bacon saved.

I even created an account to thank you for this super simple solution!

 

[emedi014]

Kudos, it worked just perfect, Like a Boss!!!

 

[xcihan]

Hello from 2021!

you saved my day

 

[MazB]

I have a pivot table that is loaded from an external data source. The data source returns about 20 mg of data so the load takes about 2 minutes.

I do not want to set the table options to 'Refresh on open' because users may open the workbook and be confused because, during the data load, the workbook appears to be frozen (yes I know the status bar displays the records being loaded, but the users don't see this).

Is there any way to display a date when the workbook was last refreshed?

This date would be controlled by the 'Refresh Data' function for pivot tables.

Thank you from 2024!