Last Data Refresh Time Stamp from Connection

[Ravi Rajendran]

Hello All, Hope you all had a great weekend.

I am trying to create time stamps on an automated report.
I have 3 Datasheets (Sheet A, Sheet B & Sheet C) which i refresh and i need to display the time stamp on Sheet D (Cell D1).

All data is being pulled in from a single provider.

Part of the problem i am facing is once the refresh is complete - it is kind of not refreshing all the pivots i had created from Sheet A, B & C and has to be done manually.

Any guidance / thoughts around using macros for this task so i can ensure all pivots are refreshed once data refresh is complete?

Regards,
Ravi

 

[Steve May]

Hi Ravi,

I had a similar problem recently, in that I was using the time a report was made as part of the "save as" file name. The way around it was to insert a vba macro that copy / pastes the time as a VALUE only. You can pribably achive this easily by using the macro recorder. Afte ryou have recorded the copy/paste, go to the "data" tab (depending on your excel version) and then click on the "refresh all button". then stop recording.

If you need to record each time stamp in a sequentioal list, then the "xl_down" command woul dbe need in the VBA as well, but it would be easier for you to copy past the code to here so I can take a look at your cell references...

cheers
Steve

 

[Ravi Rajendran]

Hi Steve, Sorry i couldn't get back to you in detail for your response.

I used the following as a macro to assist my requirement

Sub FEED()
ActiveWorkbook.Connections("Sheet A").Refresh
ActiveWorkbook.Connections("Sheet B").Refresh
ActiveWorkbook.Connections("Sheet C").Refresh
End Sub
Sub RefreshPivots()
Sheets("Pivot_1").Select
ActiveSheet.PivotTables("Sheet A_Pivot 1").RefreshTable
Sheets("Pivot_2").Select
ActiveSheet.PivotTables("Sheet B_Pivot 2").RefreshTable
Sheets("Pivot_3").Select
ActiveSheet.PivotTables("Sheet C_Pivot 3").RefreshTable
ActiveWorkbook.Save
End Sub

Function LastSavedTimeStamp() As Date
LastSavedTimeStamp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")

End Function

Then Referenced a cell to display the last updated date & time using
=LastSavedTimeStamp()

This apparently helped out - sharing it for your reference.

Thanks for writing back with what you can share / help on this subject.

Cheers,
Ravi

 

[Ajesh]

Hi Ravi,

To refresh all pivot tables i would suggest:

Sub RefreshAllPivotTables()
Dim PT As PivotTable
Dim WS As Worksheet
  For Each WS In ThisWorkbook.Worksheets
  For Each PT In WS.PivotTables
  PT.RefreshTable
  Next PT
  Next WS
End Sub

Or alternatively, you might also want to try:

ActiveWorkbook.RefreshAll

Not tried it but this should refresh all connections and objects like Pivot Table and Charts etc.

If you have any data connections they also will be refreshed.
Also before trying make sure you do not have any Protected Sheets in workbook or this might not work properly. If so unprotect the sheet (through code if required) run the code and again protect them (through code).

Thanks

 

[Ravi Rajendran]

Thanks Ajesh! I will try that.

One the other hand - i am looking for info around data refresh on Microsoft.Office.Core.DocumentProperties as i am unable to find the properties related with connections such Last Data Refresh. Any ideas?

Is it possible to display values from a specific cell in a MSG Box?
Like for instance can i use the date from cell (=LastSavedTimeStamp()) in msg box?

any tips will be really useful. Many thanks to you for your response again.

 

[Ajesh]

Hi Ravi,

Regarding DocumentProperties this might help:
http://msdn.microsoft.com/en-us/library/4e0tda25.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2


For displaying value of specific cell in MsgBox try:

Sub Prop()
Dim Msg As Variant 'You can choose as per your data type
Msg = Range("A2").Value
MsgBox (Msg)
End Sub

For getting documentproperties in a sheet:

Public Function LastSaveTime() As String
Application.Volatile
LastSaveTime = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

Public Function LastAuthor() As String
Application.Volatile
LastAuthor =ThisWorkbook.BuiltinDocumentProperties("Last Author")
End Function

to List all documentproperties:

Sub Prop()
rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.BuiltinDocumentProperties
  Cells(rw, 1).Value = p.Name
  rw = rw + 1
Next
End Sub

You might also like to look at:
http://www.cpearson.com/excel/docprop.aspx

Thanks

 

[Ravi Rajendran]

Many Many thanks Ajesh!

 

[Ajesh]

Most Welcome Ravi.