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" <(E-Mail Removed)> wrote in message
news:4B38A98C-AD41-4A38-B0DF-(E-Mail Removed)...
> 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