?
Solved

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

Posted on 2003-03-03
6
Medium Priority
?
406 Views
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?
0
Comment
Question by:grayco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 11

Accepted Solution

by:
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





WATYF
0
 
LVL 27

Expert Comment

by:Dabas
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--------

Dabas
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 13

Expert Comment

by:cri
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:

http://www.j-walk.com/ss/excel/tips/tip82.htm

http://www.j-walk.com/ss/excel/tips/tip97.htm
0
 
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")

Steve
0
 

Author Comment

by:grayco
ID: 8065582
Thanks WATYF

This worked like a charm Thanks
Glen Ray
(Grayco)
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

762 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