Date Stamp for Table Refresh

[jbvinny]

I have a database connection that gets refreshed manually by the end user. What I need is to place the date stamp of the last refresh date in a cell (say J1) so that anytime the table is refreshed the date is refreshed. Simply using =now() does not work because that cell is updated each time the workbook is opened or refreshed and I only want the value to update when the table is refreshed. Does anyone have any ideas? Maybe a UDF or a Pivot Table Refresh Event?

 

[jbvinny]

Anyone?

 

[RoryA]

You can certainly use a worksheet's pivottableupdate event to enter the current date/time in a cell:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   Me.Range("J1").Value = Now
End Sub

for example.

 

[jbvinny]

But its a data table not a pivot table. I have been able to find code that would work for a pivot table but nothing for data. Sorry if I am being dense.

 

[RoryA]

Well, you're the one who mentioned pivot tables so I assumed there was a PT involved...

If not, you can use a WithEvents Querytable variable, or use a UDF that looks at the refreshdate of the ODBCConnection (or OLEDBCOnnection) and make it volatile.

 

[jbvinny]

Sorry! I just looked at my original post and it looks like I did mention PT. Can you point me in the right direction as far as the code would go for either of those?

 

[jbvinny]

I was actually able to solve this by just creating a macro and button to refresh the table and as part of that code it also inserts the date. But obviously that only works if the button is used to refresh the connection.

 

[RoryA]

For example in the code module of the worksheet containing the query table:

Private WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
   Me.Range("J1").Value = Now()
End Sub

Private Sub Worksheet_Activate()
   Set qt = Me.ListObjects(1).QueryTable
End Sub

 

[jbvinny]

Thanks again rorya!