Display External File Properties in Excel

35 pts.
Access Database
Microsoft Access 2003
Microsoft Excel 2003
Microsoft Windows XP
Office 2003
I have an MS Access database that uses two tables (tblOrders1 and tblOrders2) that are linked to two MS Excel spreadsheet files (Orders1.xls and Orders2.xls). The Excel spreadsheet files are occasionally updated by being replaced with new files with the same file names. I want to continuously display the Excel files “date modified” dates on one of the database forms (frmOrders).  I cannot determine how to retrieve the "date modified" dates so they can be displayed on the form.  Can anyone help? I am very weak in VBA. Thanks.

Software/Hardware used:
Windows XP, MS Office 2003

Answer Wiki

Thanks. We'll let you know when a new response is added.

here is a function I use for accessing a file date in Access VBA

Function GetFileDate(FileName As String) As Date

‘ get the creation date for a file

Dim fso As FileSystemObject
Dim f1 As File

Set fso = CreateObject(“Scripting.FileSystemObject”)

If fso.FileExists(FileName) Then
Set f1 = fso.GetFile(FileName)
GetFileDate = f1.DateLastModified
End If
End Function

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • OdeMitch
    Sorry, but like I said, I'm very weak in VBA. I now have the following questions: Where do I incorporate your code into my database (do I copy and paste it in as module, event procedure, etc)? Since I need the DateLastModified dates for two (2) Excel files, will your code need to be entered and run twice, or can both dates be retrieved with the one run? Where do I enter the paths for the two Excel files? Can I display the dates on my database form by using text boxes? If so, what would be the "Control Source"?
    35 pointsBadges:
  • SidSeton
    Add the function getfiledate to a VBA module. Then use
    in the control source, dont forget the = sign at the beginning. Substitute your file name and fully qualified location for ("E:tempSwitch1.xls")
    95 pointsBadges:
  • OdeMitch
    Ok, I added the function GetFileDate to a module, and I added the =GetFileDate(file path) to the control source of a text box on the form. However, when I open the form, the Visual Basic window opens at the GetFileDate function and displays a "Compile Error: User-defined type not defined" message. If I then close the Visual Basic window and return to the form, it is displaying "#Error". Any suggestions?
    35 pointsBadges:
  • OdeMitch
    SidSeton: I removed the "As FileSystemObject" and "As File" from the two Dim lines, and it's working great now. Don't really understand how that changed anything, but it works. Thanks so much for your help.
    35 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: