TechWhirl (TECHWR-L) is a resource for technical writing and technical communications professionals of all experience levels and in all industries to share their experiences and acquire information.
For two decades, technical communicators have turned to TechWhirl to ask and answer questions about the always-changing world of technical communications, such as tools, skills, career paths, methodologies, and emerging industries. The TechWhirl Archives and magazine, created for, by and about technical writers, offer a wealth of knowledge to everyone with an interest in any aspect of technical communications.
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
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