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

[Dan Goldstein <DGoldstein -at- nuot -dot- com>]

... 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 
    Next 
    
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.

TIA,

Dan
(cross-posted to Techshoret)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Visit TechWhirl for the latest on content technology, content strategy and content development | http://techwhirl.com

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

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
http://www.techwhirl.com/email-discussion-groups/ for more resources and info.

Looking for articles on Technical Communications?  Head over to our online magazine at http://techwhirl.com

Looking for the archived Techwr-l email discussions?  Search our public email archives @ http://techwr-l.com/archives