Excel Query (ODBC) auto refresh Time Stamp

[ribhead]

Howdy -I currently have a table that refreshes every 5 minutes (from SQL server) and I would like to write the exact time it refreshes in a cell. Maybe it's really easy???? I thought I could use a worksheet change event but if the data in the table doesn't change the code won't be activated. Any help is very appreciated!!!

 

[Yongle]

This may work for you - it does when an access database query is refreshed automatically

Let me know how you get on

1. Insert the VBA below into a standard module
2. Put this formula into your cell
=LastRefresh()
3. Format your cell with your preferred time format

Function LastRefresh() As Date
    Dim lastaction As String
    Static OldValue
    Application.Volatile
    lastaction = Application.CommandBars("Standard").Controls("&Undo").List(1)

    If Left(lastaction, 7) = "Refresh" Then OldValue = Time
    LastRefresh = OldValue
End Function

 

[ribhead]

Thanks for the reply Yongle & sorry I didn't get back to you earlier. My table is refreshing automatically every 5 minutes - that's the time I'm trying to write to a cell not from manually right clicking and selecting "Refresh". Thanks anyway.

 

[Yongle]

I did not mention right-clicking on refresh
You wanted the time in a cell to update automatically and that is what it does ( tested against table from Access Database being auto-refreshed)
- I am guessing (but cannot test) that an SQL server update may also trigger the formula to recalculate

If it does not work, let me know

 

[ribhead]

Thank you Yongle - I added the code and all worked can't say I understand how it works but I appreciate it. Thank you very much!

 

[Yongle]

How it works

Application.Volatile
Some things that occur automatically in Excel auto-trigger cell formulas to recalculate and other things do not. Fortunately your SQL update triggers recalculation
By default UserDefinedFunctions are only recalculated when any of the function's arguments change. Adding Application.Volatile makes it recalculate whenever calculation occurs in any cells on the worksheet

Application.CommandBars("Standard").Controls("&Undo").List(1)
Here though, you do not want the function to change the value in a cell whenever the worksheet recalculates - it does that in the background all the time. You want the value to change ONLY when SQL auto-refresh occurs. Application.CommandBars("Standard").Controls("&Undo").List(1) provides a string telling us what Excel did last (when you click once on Undo this is the action that is undone).

Static variable
Defining a variable as Static retains its value after the function has run - here the old time value must be retained until auto-refresh occurs

Summary
1. Recalculate the cell
2. Was last action auto-refresh?
3. If YES - leave things as they are
4. If NO - replace recalculated value with the previous cell value
5. Save current value as OldValue for next time

A tiny UDF that has a lot going on!

Note of caution
Fortunately your workbook is not also auto-refreshing from an Access Database. Hopefully you are not using Pivot tables either.
Otherwise more sophistication is required in idenyifying the correct auto-refresh
Application.CommandBars("Standard").Controls("&Undo").List(1) returns string "Refresh All" for all 3 which is rather unhelpful.