|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with Excel macro to extract all custom properties of Word Document to later mass update
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 |
#2
|
||||
|
||||
Your earlier post mention that these 'custom properties' are coming from Sharepoint. If that is the case, this is far more complex than just gathering a list of the custom properties. To VBA, a custom property resides in a different area of a document structure than the custom XML files.
I replied to that earlier post and would recommend you actually explore doing this in Sharepoint by playing around with a view which includes the fields you are interested in updating. If you get the view right you can either: - 'Export to Excel' to get the complete file list and the properties you wanted. or - Use a Table view to bulk-update the fields without ever leaving Sharepoint
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Thanks for the input Guessed.
|
|
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 |