Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a time stamp on Pivot Table refresh event
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. -- --------------------------------- --- -- - Posted with NewsLeecher v3.8 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- - |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a time stamp on Pivot Table refresh event
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 "Just Learning" wrote: 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. -- --------------------------------- --- -- - Posted with NewsLeecher v3.8 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- - |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a time stamp on Pivot Table refresh event
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 "Hal" wrote: 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 "Just Learning" wrote: 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. -- --------------------------------- --- -- - Posted with NewsLeecher v3.8 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a time stamp on Pivot Table refresh event
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 "barnabel" wrote in message ... 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 "Hal" wrote: 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 "Just Learning" wrote: 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. -- --------------------------------- --- -- - Posted with NewsLeecher v3.8 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a time stamp on Pivot Table refresh event
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. -- --------------------------------- --- -- - Posted with NewsLeecher v3.8 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a time stamp on Pivot Table refresh event
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) "Mike Fogleman" wrote in message m... 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 "barnabel" wrote in message ... 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 "Hal" wrote: 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 "Just Learning" wrote: 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. -- --------------------------------- --- -- - Posted with NewsLeecher v3.8 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a time stamp on Pivot Table refresh event
Thanks. And "she" will read the intro as you suggested.
-- --------------------------------- --- -- - Posted with NewsLeecher v3.8 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a time stamp on Pivot Table refresh event
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. " wrote: 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. -- --------------------------------- --- -- - Posted with NewsLeecher v3.8 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- - -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a time stamp on Pivot Table refresh event
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. -- --------------------------------- --- -- - Posted with NewsLeecher v3.8 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
Help with Worksheet_Change event time stamp | Excel Programming | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming | |||
Pivot Table Refresh VBA Event | Excel Programming | |||
Pivot Table REFRESH Flaw -- Saves Old Data in Selection Area AFTER REFRESH | Excel Programming |