Help with Excel macro to extract all custom properties of Word Document to later mass update

anmalogo · #1 04-23-2024, 02:09 AM

Hello All,

Currently, I'm trying to develop an Excel macro, to read all the Word files in a folder and sub folder, and populate all the custom properties present in those documents. Later, the idea is to use that Excel sheet (second macro) to mass update the custom properties in the same files.

Here is the code I currently have. This code will list in column A the file path, and in column b, the file name. All custom properties from the Word document are missing. I don't know how to get them. Any ideas?. Thanks in advance for your help.

HTML Code:

Sub getfiles()
    'Get the files name from Folder and Subfolder
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object, sf
    Dim i As Integer, colFolders As New Collection, ws As Worksheet
    Dim selectedFolder As Variant
    Dim fd As FileDialog
    
    Set ws = ActiveSheet
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    'Prompt user to select a folder
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fd
        .Title = "Select Folder"
        .AllowMultiSelect = False
        If .Show = -1 Then
            selectedFolder = .SelectedItems(1)
        Else
            MsgBox "No folder selected. Operation aborted."
            Exit Sub
        End If
    End With
    
    Set oFolder = oFSO.getfolder(selectedFolder)
    
    colFolders.Add oFolder          'start with this folder
    
    Do While colFolders.Count > 0      'process all folders
        Set oFolder = colFolders(1)    'get a folder to process
        colFolders.Remove 1            'remove item at index 1
        
        For Each oFile In oFolder.Files
            If oFile.DateLastModified > Now - 7 Then
                ws.Cells(i + 1, 1) = oFolder.Path
                ws.Cells(i + 1, 2) = oFile.Name
                'ws.Cells(i + 1, 3) = oFile.DateLastModified
                i = i + 1
            End If
        Next oFile
        
        'add any subfolders to the collection for processing
        For Each sf In oFolder.subfolders
            colFolders.Add sf
        Next sf
    Loop
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to extract Excel data for Word Macro?	syl3786	Word VBA	0	10-17-2023 08:57 PM
Phantom fields (custom document properties)- where do they come from - and how can they be stopped?!	andiekit	Word	13	03-18-2021 07:02 AM
sort custom document properties	LQuinn	Word VBA	4	03-17-2021 05:19 PM
Adding Custom Document Properties	NicoleJones	Word VBA	3	03-10-2021 08:43 PM
Macro to extract bookmarked data from Word document and insert into another Word Document	VStebler	Word VBA	3	05-03-2018 05:02 PM