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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,835
Messages
5,855,909
Members
431,772
Latest member
dannyboi1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top