read folders and any document properties from excel?

Question

Hi guys... I am wanting to try something and I'm fairly sure it's possible, but not really sure!!

In MS Excel (2003) can I write a VBA script which will open a location (eg: s://public/marketing/documents/) and list all the documents located within there (filename)?

The ultimate goal would be to have the document name, date last modified, date created and modified by name.

Is this possible? I'd like to return any found values in rows on a sheet. eg: type: FOLDER, type: Word Doc etc.

Thanks for any info!

Answer 1

Done that recently. Use the DSOFile object. In Excel-VBA you first need to create a reference to Dsofile.dll ("DSO OLE Document Properties Reader 2.1" or similar). Also check you have a reference to the Office library

First you may want to select the file path which you want to examine

Sub MainGetProps()
Dim MyPath As String

    MyPath = GetDirectoryDialog()
    If MyPath = "" Then Exit Sub

    GetFileProps MyPath, "*.*"
End Sub

Let's have a nice Path selection window

Function GetDirectoryDialog() As String
Dim MyFD As FileDialog

    Set MyFD = Application.FileDialog(msoFileDialogFolderPicker)
    With MyFD
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count <> 0 Then
            GetDirectoryDialog = .SelectedItems(1)
        End If
    End With

End Function

Now let's use the DSO object to read out info ... I reduced the code to the bare necessary

Private Sub GetFileProps(MyPath As String, Arg As String)
Dim Idx As Integer, Jdx As Integer, MyFSO As FileSearch, MyRange As Range, MyRow As Integer
Dim DSOProp As DSOFile.OleDocumentProperties

    Set DSOProp = New DSOFile.OleDocumentProperties
    Set MyRange = ActiveSheet.[A2]  ' your output is nailed here and overwrites anything

    Set MyFSO = Application.FileSearch

    With MyFSO
        .NewSearch
        .LookIn = MyPath
        .SearchSubFolders = True ' or false as you like
        .Filename = Arg
        .FileType = msoFileTypeAllFiles
        If .Execute() > 0 Then
            MsgBox .FoundFiles.Count & " file(s) found."  ' to see what you will get
            For Idx = 1 To .FoundFiles.Count

                DSOProp.Open .FoundFiles(Idx) ' examine the DSOProp element in debugger to find all summary property names; not all may be filled though
                Debug.Print .FoundFiles(Idx)
                Debug.Print "Title: "; DSOProp.SummaryProperties.Title
                Debug.Print "Subject: "; DSOProp.SummaryProperties.Subject
                ' etc. etc. write it into MyRange(Idx,...) whatever

                ' now hunt down the custom properties
                For Jdx = 0 To DSOProp.CustomProperties.Count - 1
                    Debug.Print "Custom #"; Jdx; " ";
                    Debug.Print " Name="; DSOProp.CustomProperties(Jdx).Name;
                    If DSOProp.CustomProperties(Jdx).Type <> dsoPropertyTypeUnknown Then
                        Debug.Print " Value="; DSOProp.CustomProperties(Jdx).Value
                    Else
                        Debug.Print " Type=unknowwn; don't know how to print";
                    End If
                    MyRow = MyRow + 1
                Next Jdx
                DSOProp.Close
            Next Idx
        Else
            MsgBox "There were no files found."
        End If
    End With
End Sub

and that should be it

good luck MikeD