RE: Most recent file modified in a folder, in a table

Subject: RE: Most recent file modified in a folder, in a table
From: Dan Goldstein <DGoldstein -at- nuot -dot- com>
To: "TECHWR-L (techwr-l -at- lists -dot- techwr-l -dot- com)" <techwr-l -at- lists -dot- techwr-l -dot- com>
Date: Wed, 3 Feb 2016 23:04:59 +0000

... and here's the solution, from Stephen Posey at the WORD-PC list. Besides the disclaimer of no error checks, Stephen also notes that "it will not drill down into subdirectories, although it could be modified to do so":
This works for me (note: no error catching for valid paths, etc.):
This assumes the first table in the document has two columns. First column has paths, second column to receive oldest timestamp of files in that path

Public Sub DirPathDates()
Dim ThisTableRow As Row
Dim ThisPath As String
Dim ThisFileName As String
Dim ThisFileDate As Date
Dim OldestFileDate As Date

For Each ThisTableRow In ActiveDocument.Tables(1).Rows
OldestFileDate = "12/31/2999" ' For each path, initialize at some future date
ThisPath = ThisTableRow.Cells(1).Range.Text ' Get the text of the first column
ThisPath = Left$(ThisPath, Len(ThisPath) - 2) ' Get rid of end-of-cell marker
ThisFileName = Dir(ThisPath + "\*.*") ' File a file in the folder
While ThisFileName <> "" ' If found one
ThisFileDate = FileDateTime(ThisPath + "\" + ThisFileName) ' Get timestamp
If ThisFileDate < OldestFileDate Then ' If timestamp is before Oldest
OldestFileDate = ThisFileDate ' Update oldest to this timestamp
End If
ThisFileName = Dir ' Get next file in folder
Wend ' Keep going as long as you find files in folder
ThisTableRow.Cells(2).Range.Text = OldestFileDate ' Put into column 2 the oldest timestamp

End Sub

-----Original Message-----
From: Dan Goldstein
Sent: Tuesday, February 02, 2016 5:19 PM
To: TECHWR-L (techwr-l -at- lists -dot- techwr-l -dot- com)
Subject: Most recent file modified in a folder, in a table

I'd like to use either an Excel spreadsheet or a Word table to do the following:

* I create a column that lists certain folder paths:
* C:\Users\dgoldstein\Documents\Unicorns
* C:\Users\dgoldstein\Documents\Milkshakes
* C:\Users\dgoldstein\Documents\ChocolateMilkshakes, etc.
* Excel or Word populates the column next to it with the most recent date that any file in that folder was modified.
* If the macro/field code requires that the folder path not have spaces, etc., I can adjust for that.


(cross-posted to Techshoret)
Visit TechWhirl for the latest on content technology, content strategy and content development |


You are currently subscribed to TECHWR-L as archive -at- web -dot- techwr-l -dot- com -dot-

To unsubscribe send a blank email to
techwr-l-leave -at- lists -dot- techwr-l -dot- com

Send administrative questions to admin -at- techwr-l -dot- com -dot- Visit for more resources and info.

Looking for articles on Technical Communications? Head over to our online magazine at

Looking for the archived Techwr-l email discussions? Search our public email archives @

Previous by Author: Most recent file modified in a folder, in a table
Next by Author: RE: Need Feedback from Technical Editors
Previous by Thread: RE: Most recent file modified in a folder, in a table
Next by Thread: change PowerPoint from 4:3 to 16:9 without stretching images

What this post helpful? Share it with friends and colleagues:

Sponsored Ads