Report Refresh Time-(Excel)

平平淡淡

如果你经常用Excel通过数据库提取数据然后用PivotTable或PivotChart做一个Report，发给你经理希望每天打开这个Report就可以看到最新的数据。可以做如下操作：


Click Data Tab then From Other Sources to choose your datasource to add PivotTable


Alt+F11 to open VBA window


Right Click any objects in project window and choose Insert the module



Paste the below code to create a user defined function
Function LastRefreshTime(Rng As Range) As Variant
Dim PT As PivotTable
Set PT = Rng.Cells.PivotTable
LastRefreshTime = PT.RefreshDate
End Function


Use the function.Note reference any cell in PivotTable, or function will return error.


It will return an number with decimal,but we want a time.Select the cell with fomular and press CTRL+1 to bring up FormatCell dialog box,click Number tab and click Custom and type "dd/mmm/yyy hh:mm" in "Type" field.



It now give us the date and time the report was refreshed. then add some text above the Pivottable to tell your boss he is looking at the lastest data. Finally click anywhere in the PivotTable report. This displays the PivotTable Tools, adding an Options and a Design tab. On the Options tab, in the PivotTable group, click Options. In the PivotTable Options dialog box, on the Data tab, select the Refresh data when opening the file check box. Every morning your boss open the file,the pivottable will automatically refresh to get the latest data.

希望对有需要的朋友有所帮助。

平平淡淡

补充一点，或许有人会问为什么不用NOW()because it's volatile and it recalculates if you have other formulars in the same sheet.

sinkingmoon

The function should have returned date type, instead of variant. Also the input variable probably is better to be pivot table names Such as pivottable as string

1212qwe

william.wangzy

高手，mark

心平气和

cllinzhen

厉害

wangxiaom