Share via

capture a list of selected file properties

Anonymous
2012-04-21T10:18:45+00:00

I'm looking for a way to get Excel to list the properties of MS Word files in a given folder. In particular I need the list to include total editing time. I have come across various bits of script for pulling down the more usual file properties like date created, etc., but nothing where I can figure out how to capture editing time. Any help will be much appreciated.

(I am fairly familiar with coding and running macros in Word, but my overall VBA skills - and Excel skills - are pretty basic!)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2012-04-22T09:14:48+00:00

David Newmarch,

Try this approach ... Save a new Excel file as .xlsm (macro enable) in a new folder near top of the tree (so file path isn't too long). Copy your Word files into the same folder. Open the Excel file and the VB editor (Alt+F11) and click Tool > References, and select  Microsoft Word 12.0 Object Library (if not already selected). Copy the code below into a module and run -- it currently saves the Word doc props into sheet 1 of the Excel file, based on your wishlist.

Sub Wd_Doc_Props()

'

Dim p As String, r As Long, xlWb As Excel.Workbook, xlWs As Excel.Worksheet

Dim wdApp As Word.Application, wrd As String, wdDoc As Word.Document

'

On Error Resume Next

Set wdApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then 'Word isn't already running

    Set wdApp = CreateObject("Word.Application")

End If

On Error GoTo 0

'

Set xlWb = Application.ActiveWorkbook

Set xlWs = xlWb.ActiveSheet

'

xlWs.Cells(1, 1) = "Filename"

xlWs.Cells(1, 2) = "Creation date"

xlWs.Cells(1, 3) = "Last save time"

xlWs.Cells(1, 4) = "Total editing time"

xlWs.Cells(1, 5) = "Number of words"

xlWs.Cells(1, 6) = "Number of bytes"

'

r = xlWs.Cells(Rows.Count, "A").End(xlUp).Row + 1

'

p = xlWb.Path

'

wrd = Dir(p & "\*.*")

'

Do While wrd <> ""

'

    If Right(wrd, 4) = ".doc" Or Right(wrd, 5) = ".docx" Then

    '

        Set wdDoc = wdApp.Documents.Open(p & "" & wrd)

        '

        On Error Resume Next

        '

        xlWs.Cells(r, 1) = wdDoc.Name

        xlWs.Cells(r, 2) = wdDoc.BuiltinDocumentProperties("Creation date").Value

        xlWs.Cells(r, 3) = wdDoc.BuiltinDocumentProperties("Last save time").Value

        xlWs.Cells(r, 4) = wdDoc.BuiltinDocumentProperties("Total editing time").Value

        xlWs.Cells(r, 5) = wdDoc.BuiltinDocumentProperties("Number of words").Value

        xlWs.Cells(r, 6) = wdDoc.BuiltinDocumentProperties("Number of bytes").Value

        '

        r = r + 1

        '

        wdApp.Documents.Close savechanges = False

    '

    End If

    '

    wrd = Dir()

'

Loop

'

wdApp.Quit

'

End Sub

___________

Regards, Tom

Was this answer helpful?

5 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-04-22T10:22:57+00:00

    It works beautifully. Many thanks.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-04-21T19:12:22+00:00

    Thanks, Chip, for the reply, but I'm afraid I'm way out of my depth here - no idea where to start with what you have indicated!

    I'm looking for a way to capture a list (in Excel) of all the MS Word files in a selected folder, together with their properties (including total editing time). The properties that would be useful to me (my wish list!) would be Filename, Date created, Date modified, Date last saved, Size,Total editing time, ang mabe also Word count.

    I found one Excel add-in that creates a list of all files in a selected folder (not just Word files) giving Filename, Date created, Date modified, Date last saved, Size, but not Total editing time or Word count (which would be properties specific to Word files).

    If I can capture this in Excel then I can quickly get a grand total of editing time on the various files in a project. Total word count would be a lesser priority (and document word count can be erratic and unreliable anyway).

    Is this feasible?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-04-21T11:59:01+00:00

    See http://www.cpearson.com/excel/DocProp.aspx for code for working with properties, both reading and writing.

    Was this answer helpful?

    0 comments