Last Update to Linked Spreadsheet

[Two Gun]

I export data from a system to an excel spreadsheet, which is linked to my database. When I run reports in Access, is there any way to identify when the linked table was last updated? This would be helpful to users if I could add a text box that shows "Data current as of" some date. Thanks.

[Robeen]

I use the DateValue function in VBA to check if files that I am importing from Excel have the current date.

For your case, you can create a function - like this:

 
Function Get_File_Date()
Dim FilePath, FileName As String
Dim FileDate As Date
 
FilePath = "C:\DirectoryName\"
FileName = "FileName.xlsx"
 
Get_File_Date = DateValue(FileDateTime(FilePath & FileName))
 
End Function

and then in your query design view, add a field like this:

 
FileDate: Get_File_Date()

When you run your query, it will put the Modified Date of your spreadsheet in that field of your query.

Let me know if this helps!

[Two Gun]

Works like a charm! Thanks much.

[Robeen]

Happy to help.

One problem I've noticed with this. If the table is Large - then the query will be slow - because it is hitting that function for each row of data.
Whenever I've used this trick in a query - there hasn't been another way and the reports that use these queries run early in the morning before I get to work anyway - so I don't notice the lag!!

[Two Gun]

Robeen - This has worked perfectly since you shared your solution ... thanks again. However, the date is displayed as 1/9/12. Any chance VBA would allow me to format a long date ... Monday, January 9, 2012? I've tried everything I can think of, with no luck.

[Robeen]

You can use the Format() function in your Query to return the date in the format you mentioned.

Instead of:

 FileDate: Get_File_Date()

Try:

 FileDate: Format(Get_File_Date(), "dddd, mmm d yyyy")

[Two Gun]

Again ... works perfectly. Thanks much!

[Robeen]

Glad I could help!

Happy Trails!