Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Excel? How can I get a macro to retrieve "Last Modified" timestamp

Posted on 2003-03-03
Medium Priority
Last Modified: 2008-03-03
Greetings Experts
I have a system of Excel files that provide our Maintenance group with daily passdowns.
I want to write a macro that will retrieve the "Last Modified" time stamp from four different excel files.
The "Last Modified" timestamp I'm refering to is the one visible with "Details" selected while exploring the file trees.
I need to obtain the timestamp that shows the last time these files were saved.  The target files may or may not be open when this macro is run.

Any Thoughts?
Question by:grayco
LVL 11

Accepted Solution

WATYF earned 800 total points
ID: 8062101
This is a very simple way to accomplish what you want. Obviously, you would use something other than a MsgBox to display your results (i.e. enter them in a particular Cell or something), but you get the picture.


Dim FlDir As String
Dim FlNm1 As String
Dim FlNm2 As String
Dim FlNm3 As String
Dim FlDt1 As String
Dim FlDt2 As String
Dim FlDt3 As String

Sub Test()

    ' Enter the directory of the files here.
    FlDir = "C:\My Files\"

    ' Enter the file names here.
    FlNm1 = "File1.xls"
    FlNm2 = "File2.xls"
    FlNm3 = "File3.xls"

    FlDt1 = FileSystem.FileDateTime(FlDir & FlNm1)
    FlDt2 = FileSystem.FileDateTime(FlDir & FlNm2)
    FlDt3 = FileSystem.FileDateTime(FlDir & FlNm3)
    MsgBox FlNm1 & " was last modified on " & FlDt1
    MsgBox FlNm2 & " was last modified on " & FlDt2
    MsgBox FlNm3 & " was last modified on " & FlDt3

End Sub

LVL 27

Expert Comment

ID: 8062583
I would suggest using the FileSystemObject

In the Visual Basic window click on Tools -> References check the

Microsoft Scripting Runtime option

The following is a Paste out of Help for DateLastModified

---------Start of Paste--------
The following code illustrates the use of the DateLastModified property with a file:

Sub ShowFileAccessInfo(filespec)
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    s = UCase(filespec) & vbCrLf
    s = s & "Created: " & f.DateCreated & vbCrLf
    s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
    s = s & "Last Modified: " & f.DateLastModified  
    MsgBox s, 0, "File Access Info"
End Sub

--------End of Paste--------

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 13

Expert Comment

ID: 8062967
The filesystem object requires Windows Scripting Host, which could be disabled if the admin takes security seriously.

Perhaps you can use the following:


LVL 43

Expert Comment

by:Steve Knight
ID: 8063744
Or just return it to a cell using this in a module:

private function GetFileDateTime(filename as string) as double
  on error resume next
  GetFileDateTime=DateValue(FileDateTime(filename$)) + TimeValue(FileDateTime(filename$))
end function

Then to show in a cell =GetFileDateTime("C:\whatever.xls")


Author Comment

ID: 8065582
Thanks WATYF

This worked like a charm Thanks
Glen Ray

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question