Showing Date Modified

gazzcrawford

New Member
Joined
Oct 5, 2006
Messages
7
I have a master spreadsheet which draws data from other spreadsheets (5 in total) on the Master spreadsheet I would like to show when the last time each of the other spreadsheets were saved (date modified) can this be done??
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
something like this. It requires you to set a reference to the Microsoft Scripting Runtime in the VBE. You could turn this into a UDF if you wanted to. You could also add an if statement so that it only output the names of the files you wanted. It all depends on exactly what you are looking for

Code:
Sub GetMostRecentFile()
'macro to get all the dates of each file
'requires reference set to Microsoft Scripting runtime
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim i As Integer

        
    'set path for files - change for your folder
    Const myDir As String = "C:\Temp\"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file and get date last modified. Output on WorkSheet
    i = 1
    For Each objFile In myFolder.Files
        With ThisWorkbook.Sheets("Sheet1")   'change for your sheet name
            .Cells(i, 1) = objFile.Name
            .Cells(i, 2) = objFile.DateLastModified
            i = i + 1
        End With
        
    Next objFile
    
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
This is an example of a UDF that you could use. To use this, you must open the VBE (alt+F11). In the explorer window you need to insert a new module. Paste this code in the new module. Then choose Tools->References and find the Microsoft Scripting runtime and tick the check box.

Now save your file. Go inti the Excel and choose Insert->Function (or Fx button). Click on user defined functions and this function will appear. Use it like any other function

Code:
Function GetLastModifiedDate(strPath As String, strFileName As String)
'returns last modified date. requires Full path and filename as inputs
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
           
       
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(strPath)
    For Each objFile In myFolder.Files
        If objFile.Name = strFileName Then
            GetLastModifiedDate = objFile.DateLastModified
            Exit Function
        Else
            GetLastModifiedDate = "File Not Found"
        End If
        
    Next objFile
            
    Set FileSys = Nothing
    Set myFolder = Nothing


End Function
 

gazzcrawford

New Member
Joined
Oct 5, 2006
Messages
7
BINGO!!! Thank you very much, few extra brownie points with the boss!!

Cheers again!

Gazz
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,038
Members
410,583
Latest member
gazz57
Top