Hello,

I have an Excel file with several pivot tables in different worksheets that
I would like automate the following:
1) When the pivot tables are refreshed, it shows the date/time stamp they
were refreshed on each sheet.
2) I created a scheduler task to open the file, but need to automate it to
run the refresh all macro when it is opened once only every morning Mon to
Frid at 7AM.

I created a refresh all macro to refresh all pivot table. Thanks

In your refresh macro, you could add (or update) a named range/constant in
the workbook to reflect the Refresh Date/Time and then display that somewhere
on the pivot sheet. Of course, this wouldn't catch a change if someone
manually refreshed it.

"Cam" wrote:

Hello,

I have an Excel file with several pivot tables in different worksheets that
I would like automate the following:
1) When the pivot tables are refreshed, it shows the date/time stamp they
were refreshed on each sheet.
2) I created a scheduler task to open the file, but need to automate it to
run the refresh all macro when it is opened once only every morning Mon to
Frid at 7AM.

I created a refresh all macro to refresh all pivot table. Thanks

Try using the pivot table's refreshDate property. The trick is in retrieving
the updated refreshDate when it is refeshed. Unfortunately, there are no
pivot table events (that I know of), but you can use the change event of the
respective worksheet . Your change event could call a sub like the one
below, and place the value in a nearby cell rather than a msgbox.

Sub refreshDate()
Set pvtTable = Worksheets("Yearly").PivotTables("Yearly")
dateString = Format(pvtTable.refreshDate, "Long Date")
MsgBox "The data was last refreshed on " & dateString
End Sub


"Cam" wrote:

Hello,

I have an Excel file with several pivot tables in different worksheets that
I would like automate the following:
1) When the pivot tables are refreshed, it shows the date/time stamp they
were refreshed on each sheet.
2) I created a scheduler task to open the file, but need to automate it to
run the refresh all macro when it is opened once only every morning Mon to
Frid at 7AM.

I created a refresh all macro to refresh all pivot table. Thanks

but if you refresh the pivot table this event gets fired:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

so all one would need to do is add a line like
Range("A1")="Refreshed at " & now()


"eric_powell" wrote in message
...
Try using the pivot table's refreshDate property. The trick is in
retrieving
the updated refreshDate when it is refeshed. Unfortunately, there are no
pivot table events (that I know of), but you can use the change event of
the
respective worksheet . Your change event could call a sub like the one
below, and place the value in a nearby cell rather than a msgbox.

Sub refreshDate()
Set pvtTable = Worksheets("Yearly").PivotTables("Yearly")
dateString = Format(pvtTable.refreshDate, "Long Date")
MsgBox "The data was last refreshed on " & dateString
End Sub


"Cam" wrote:

Hello,

I have an Excel file with several pivot tables in different worksheets
that
I would like automate the following:
1) When the pivot tables are refreshed, it shows the date/time stamp they
were refreshed on each sheet.
2) I created a scheduler task to open the file, but need to automate it
to
run the refresh all macro when it is opened once only every morning Mon
to
Frid at 7AM.

I created a refresh all macro to refresh all pivot table. Thanks