|
|
本帖最后由 平平淡淡 于 2013-1-25 23:21 编辑
如果你经常用Excel通过数据库提取数据然后用PivotTable或PivotChart做一个Report，发给你经理希望每天打开这个Report就可以看到最新的数据。可以做如下操作：
2013-1-25 22:57:12 上传
下载附件 (99.57 KB)
Click Data Tab then From Other Sources to choose your datasource to add PivotTable
2013-1-25 22:57:15 上传
下载附件 (76.16 KB)
Alt+F11 to open VBA window
2013-1-25 22:57:16 上传
下载附件 (62.37 KB)
Right Click any objects in project window and choose Insert the module
2013-1-25 23:05:00 上传
下载附件 (75.85 KB)
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
2013-1-25 22:57:17 上传
下载附件 (73.94 KB)
Use the function.Note reference any cell in PivotTable, or function will return error.
2013-1-25 22:57:19 上传
下载附件 (168.23 KB)
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.
2013-1-25 22:57:20 上传
下载附件 (83.38 KB)
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.
希望对有需要的朋友有所帮助。
|
上一篇：我该如何是好啊？下一篇：已经或计划在澳洲从事医师的朋友请进(AMC MCQ)
|
雷达卡
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
