Change meta data for many documents at once

Hi guys,

Im wondering if its possible to have vba change the meta data for many documents?

The only way I can think of, is if I have excel open every word document, excel and pdf, make changes and save and close. And im a bit worried about doing that approximately 2000 times in one macro.
I'd imagine it would take a very long time, unless someone knows a few tricks.


I don't think I would be able to get excel to update .jpeg metadata like EXIF information


I'm wondering if anyone knows a better way to do this or has some ideas?

Thanks,
Jimmy

Here's a recent mass replacement:

Sub Fubar(): Dim UR As Range, ws As Worksheet
For Each ws In Worksheets
Set UR = ws.UsedRange
UR.Replace "123Foo", "123Foobar", xlWhole
Next
End Sub

to replace all instances of 123Foo with 123Foobar - it's as good as new, only dropped once

Hi xladept,

I'll play around with it, but I don't think that would work for word and pdf or other types of files.

For Each ws In Worksheets

Don't you mean workbooks? Because the properties within a book's worksheets will all be the same..

From what I can tell, that would only work with workbooks, what about pdf's and word documents?

Thanks,

Hi Jimmy,

No, it's worksheets, just as written. And, yes, it's good for just the excel

Is there a way to do it for PDFs and other types of documents?

vba>tools>references...>Adobe? etc

thanks

Also, I'm hoping to do this to many files, not just the few that are open.

I'll have a look and play around with your idea soon.

On both Word and PDF, by googling, they suggest that you convert the PDF to Word and then back to PDF.

Do you have Adobe? I don't

And I haven't got the code for the Word conversion although the VBA is similar the naming is strange to me

word vba is a bit different, messes things up a bit.

what I have for word, almost works, except it doesn't because read and write permissions. kinda works if you use a template document, but even then it's opening changing, saving and closing documents. I was hoping there was a way of doing it with the shell or something?

Below is what I've got for word:

Module1:

Dim X As New EventClassModule

Sub Register_Event_Handler()
 Set X.App = Word.Application
End Sub

ClassModules:

EventClassModule:
Public WithEvents App As Word.Application
'
'Private Sub App_DocumentBeforeSave _
'() ' (ByVal Doc As Document, _
' SaveAsUI As Boolean, _
' Cancel As Boolean)
'
' Dim intResponse As Integer
'
' intResponse = MsgBox("Do you really want to " _
' & "save the document?", _
' vbYesNo)
'
' If intResponse = vbNo Then Cancel = True
'End Sub
'

Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
'Sub TitleWithoutExtensionsVersionAutomatic()
'20180122
'Version1, requires to be ran manually
'Allows FieldCode "Title" to be used through the document without the extension or directory being displayed.

'Insert > Text > Quick Parts > Fields... > "Title" will reflect the named document without the extension

'FieldCode "FileName" typically is used but may or may not show the extension, depending on Explorer Settings.

'----------Dimensions
Dim str01 As String                             'Text to call "Title" property
Dim Str02 As String                             'User Imput for the directory folder separators
Dim DocProp01 As DocumentProperty               '
Dim Long01 As Long, Long02 As Long              'Position of the "." and the "\"
'----------Dimensions
'----------Varables
Str02 = "\"                                     'UserImput for the Folder Separator, if not found defaults to "/", and then "\"
'----------Varables
'----------AA001
'Determines where to cut the FullName strings, and creates it.
str01 = ActiveDocument.FullName
Long01 = InStrRev(str01, ".", -1, vbTextCompare)
Long02 = InStrRev(str01, Str02, -1, vbTextCompare)
    'if User Separator not found, uses "/" and then "\"
    If Long02 = 0 Then
    Long02 = InStrRev(str01, "/", -1, vbTextCompare)
        If Long02 = 0 Then
            Long02 = InStrRev(str01, "\", -1, vbTextCompare)
        End If
    End If
'If cannot find any folder separators, stops function
If Long02 = 0 Then
    GoTo line1:
End If

Long02 = Long02 + 1
str01 = Mid(str01, Long02, Long01 - Long02)
'----------AA001
'----------AB001
'Goes through and renames the Title property to the filename (without directory, without extension)
For Each DocProp01 In ActiveDocument.BuiltInDocumentProperties
If DocProp01.Name = "Title" Then
    Debug.Print DocProp01.Value
DocProp01.Value = str01
End If
Next DocProp01
'----------AB001
'----------AC001
'Updates all fields and saves
line1:
ActiveDocument.Fields.Update
'ActiveDocument.Save
'----------AC001
End Sub

This Document

Private Sub Document_New()
    Register_Event_Handler
End Sub

Private Sub Document_Open()
    Register_Event_Handler
End Sub

I wonder whether events are the way to go - not what I envisioned

*Bumping*

Still looking for a good method to do this.
Thanks,
Jimmy