Need a time stamp on Pivot Table refresh event

[here]

I have a pivot table that will refresh source data (upon opening),
from an AS400 database through an ODBC connection.

I would like to have a time stamp on the pivot table that shows the
exact time of the source data was pulled from the AS400 database.

Right now I just have a formula =now() which can change if moving
from tab to tab and doesn't mean the source data was refreshed at
that time.

I'm just starting to learn Macros/VBA so if there is some code
snipit that would do the trick please be as detailed as possible as
to how I would include it.

Thanks in advance for your help.

 

[Guest]

There's probably more than one way to do this but here is the solution I came
up with.

Sub NOW()
Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Selection.HorizontalAlignment = xlLeft
End Sub

 

[Guest]

2 comments:
1) "Now" is a real VBA function so it is a bad idea to create a function
with the same name
2) Wouldn't this be a much easier way to do it?
Sub timeStamp()
Cells(1, 1) = TimeValue(Now)
End Sub

Peter Richardson

 

[Mike Fogleman]

The line of code from Barnabel should be added to the end of the code that
refreshes the pivot table. If the code is in the Workbook_Open event,
specify the worksheet the cells are on:

Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
Worksheets("Sheet1").Cells(1, 1) = TimeValue(Now)
'for Date & Time =Date & " " & TimeValue(Now)
End Sub

Be sure to format the cell for Time.

Mike F

 

[here]

Thanks to all that responded.

The only thing is that the refresh on open is done by selecting the
option within table options of the pivot table. I would assume the
suggestion given would mean I should deactivate the option and write
a macro that runs on open and then does both refresh and timestamp.
Nice one to cut my teeth on. I'll give it a go and let you know the
results.

For my first question in the newsgroup I've had great response and
help. Thanks so much.

 

[Shah Shailesh]

Use Pivottable update Event for Excel XP + as under.

Put below code in worksheet's code module having Pivottable.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Worksheets("Sheet1").Cells(1, 1) = TimeValue(Now)

End Sub

As OP is new to macro, he may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
If You Can't Excel with Talent, Triumph with Effort.

http://in.geocities.com/shahshaileshs/menuaddins
(Free addins old\classic Office Menu-2003 for Office-2007)

 

[here]

Thanks. And "she" will read the intro as you suggested.

 

[Dave Peterson]

I'm not sure what suggestion you'll use, but I'd

with somerangevariablehere 'like Worksheets("Sheet1").range("a1")
.numberformat = "mm/dd/yyyy hh:mm:ss"
.value = now
end with

I don't think I'd use timevalue() in this case.

 

[here]

Thanks Dave. No suggestions is a bad suggestion. I'll take them
all into account and see what works best.

I don't put the source data on one sheet and have the pivot table on
another. The pivot table refreshes upon opening and pulls the
current info from the database into the pivot table. This is set
based on an option in table options. I'm probably going to change
this and create a macro to refresh and timestamp on open.

I also had a suggestion to check out a web site, which has some
great info. Thanks Shailesh.