OLEDB Connection has no refresh date
I haven't found a satisfactory solution but this may help you if you desperately need to know went the connection was updated. This may also depend on the sort of connection you have. Disclaimer this solution is more of a hack than a professional solution but seems to work until now. Here is the plan:
1 The Dummy Display
Display a piece of data from your connection in a worksheet. This worksheet Sheet1
may be Hidden
or VeryHidden
. Doesn't really matter.
2 The Event
Modify the Worksheet_Change
Event as following:
Private Sub Worksheet_Change(ByVal Target As Range)RefreshDate (Now())End Sub
3 Modul
On top of that you want a modul that provides functionality for storing and accessing the RefreshDate
property on another sheet. You may want to do it with an object stored in the Thisworkbook
property but that's not save from destruction as far as I can tell.
Here the code:
Sub RefreshDate(D As Date)Sheet2.Range("A1").Value = DEnd SubPublic Function GetRefreshDate() As DateGetRefreshDate = Sheet2.Range("A1").ValueEnd Function
4 Rinse and Repeat for all Connections
You now need to do this for all connections that don't work with the RefreshDate
. You may want to save all Dates in one worksheet and have one worksheet for each connection.
Is this solution ugly? Yes it is. Does it work? Yes it does.
The basic idea is the following: Every time the connection gets refreshed the worksheet will change, this will trigger the event : Worksheet_Change
now you can save the date in order to access it later.
If you find other means to access an event whenever a connection is refreshed this should do the trick too. If you find other means to save the RefreshDate
it will do the trick.
If the refreshDate
is not filled, probably you are out of luck.
As a workaround, you could keep track about the refresh by yourself. Starting point is the afterRefresh
-Event of a table. For this you have to add the following code to the Workbook
-Module (will not work with a regular module as the With Events
need a class
.
Option ExplicitPrivate WithEvents table As Excel.QueryTablePrivate Sub table_AfterRefresh(ByVal Success As Boolean) Debug.Print table.WorkbookConnection.name & " refreshed. (success: " & Success & ")" If Success Then Call trackRefreshDate(table.WorkbookConnection.name, Now) End IfEnd Sub
Now you just need a logic to save the refresh event. In my example, I save it as name on workbook level, of course you could also save it in a (hidden) sheet. Put this into a regular module:
Sub trackRefreshDate(tableName As String) Dim nameObj As Name, nName As String Set nameObj = Nothing nName = "Refresh_" & tableName On Error Resume Next ' Check if name already exists Set nameObj = ThisWorkbook.Names(nName) On Error GoTo 0 Dim v v = Format(Now, "dd.mm.yyyy hh:MM:ss") If nameObj Is Nothing Then ' No: Create new Call ThisWorkbook.Names.Add(nName, v) Else nameObj.Value = v End IfEnd SubFunction getRefreshDate(tableName As String) Dim nName As String nName = "Refresh_" & tableName On Error Resume Next getRefreshDate = Replace(Mid(ThisWorkbook.Names(nName), 2), """", "") On Error GoTo 0 End Function
Why not simply add a column in your SQL source which you connect to (be it a view or procedure)?. Add there the column RefreshTime=GETDATE()
. Every time user pulls the data from SQL they have RefreshTime
in returned results.
If you need to store information when user refreshed SQL source do it as well in SQL database. Make stored procedure:
create stored procedure ShareMySource as-- part one, prepare dataselect * from MySQLTable;-- part two, get user datainsert into dbo.LogBookselect RefreshTime=getdate(), User = ORIGINAL_LOGIN()
The table LogBook must be created first.