Showing Date Modified

[gazzcrawford]

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??

 

[lozzablake]

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

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]

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

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]

BINGO!!! Thank you very much, few extra brownie points with the boss!!

Cheers again!

Gazz