Display Last Modified Date for List of File Names

[CorrieAnn]

I have the following code which, when called, will display the folder contents as a list on a dashboard in Excel:

Sub DisplayInProgressBeth()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

Range("S29:S38").Select
Selection.ClearContents
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
'Set objFolder = objFSO.GetFolder("\\SVR-CC-DC01\Shared\AMLC\AMLC - In Progress\Beth")<--old location on the S: drive
Set objFolder = objFSO.GetFolder("\\Svr-cc-dc01\amlc\AMLC - In Progress\Beth")
i = 1
'loops through each file in the directory
For Each objFile In objFolder.Files
'select cell
Range(Cells(i + 28, 19), Cells(i + 28, 19)).Select
'create hyperlink in selected cell
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.path, _
TextToDisplay:=objFile.Name
i = i + 1
Next objFile
Range("S24").Select

End Sub

However, what I need is to also display the last modified date/time in the adjacent cell on the dashboard (starting in T29). I'm not sure how to call this for each individual, corresponding file found in the folder.

Just to be clear, the result should look something like this:
___________COLUMN-S_____COLUMN-T
ROW 29.....File1.xlsm..........2/24/20 5:24PM
ROW 30.....File2.xlsm..........2/24/20 3:16PM
ROW 31.....File3.xlsm..........2/25/20 8:03AM

Ideas/solutions?

Thanks!

 

[Fazza]

untested. what about inserting this line before the i=i+1 line ?
ActiveCell.Value = FileDateTime(objfile.Path & "\" & objfile.Name)

I'm not sure what is the .Path and the .Name but it should be easy enough to modify the idea. FileDateTime function should do the trick. You may need some formatting too.

 

[CorrieAnn]

untested. what about inserting this line before the i=i+1 line ?
ActiveCell.Value = FileDateTime(objfile.Path & "\" & objfile.Name)

I'm not sure what is the .Path and the .Name but it should be easy enough to modify the idea. FileDateTime function should do the trick. You may need some formatting too.

Thank you for your input.

I can't get that to work. And, I want it to appear in the column adjacent, not as a concatenated value.

 

[Fazza]

a disconnect from my brain to the keyboard!
where I wrote activecell should be activecell.offset(,1)
Sorry about that. The offset(,1) causes entry to the cell 0 rows down and 1 column right from the cell that just received the hyperlink

again I've only glanced at this so not sure what the values are for objfile.path and objfile.name
please use whatever is needed for the full file path - you need FileDateTime("c:\directory\subdirectory\etc\filename.ext")
OK?

 

[CorrieAnn]

I know it has been some time since I have been able to visit this project. COVID does funny things to project timelines these days.

Your solution implies the file name is known to me and therefore I can plug it into the objfile.name variable. On the contrary, the solution I have coded above tells ME which files are located in the folder. I do not know what they are until another team member places the files in the folder. When I press a refresh button on my dashboard, the script runs and displays the contents of the folder on my screen (spreadsheet). All I want to to also display the date modified field typically shown in the windows explorer folder so that I can see the chronological order of the files placed in folder.