using a Userform to change the document properties or tags

[dellehurley]

Hi
I have a userform which I use to move and rename files, populate an associated database and hopefully update the documents properties.
I have all the first parts working but I cannot get the documents properties sub to work. I feel like I have tried what feels thousands of variations.

Additional Info- all the files are jpg or pdfs. They are all moved and stored in the same folder which is where the excel database file is saved too. All the issues are in regards to objects not being declared.
the current error is runtime 91 -object variable or with block variable not set.

This is what I have so far

Dim fileName, folderName, newName As Variant
Dim NewNameObj As Image   'this was my most recent change
Dim EventDate As DocumentProperty
Dim Tags As DocumentProperty
Dim Description As DocumentProperty
Dim Evt, EvtDt, Tgs, Desc As String

folderName = ThisWorkbook.Path & "\"
fileName = frmNewEntry.txtFileName.Value
newName = folderName & fileName
'Set NewNameObj = newName.CustomDocumentProperties

Evt = frmNewEntry.cmbEvent.Value   'or ThisWorkbook.Sheets("Database").Range("D2").Value
Desc = frmNewEntry.txtDescription.Value  'or ThisWorkbook.Sheets("Database").Range("I2").Value
Tgs = ThisWorkbook.Sheets("Database").Range("J2").Value
EvtDt = frmNewEntry.txtDate.Value   'or ThisWorkbook.Sheets("Database").Range("H2").Value    'NB. I wish to keep this as a string even though it is a date as the info is not always a full date, it is often an estimate eg. Abt 1940
With NewNameObj.CustomDocumentProperties
    .Add Name:="nEvent", LinkToContent:=False, Type:=msoPropertyTypeString, Value:=Evt   'BTW -Link to content Honestly I'm not sure what this is so it may be incorrect
    .Add Name:="Description", LinkToContent:=False, Type:=msoPropertyTypeString, Value:=Desc   'Type names in an effort to try something else.
    .Add Name:="Tags", LinkToContent:=False, Type:=msoPropertyTypeString, Value:=Tgs
    .Add Name:="EventDate", LinkToContent:=False, Type:=msoPropertyTypeString, Value:=EvtDt

End With

I appreciate any help or suggestions.
Thanks
Dannielle

 

[Dan_W]

Hi, I could be wrong, but my understanding is the Custom Document Properties are available only for Office documentation - ie., Word, Excel, Powerpoint, Access, etc. In order to add/change/delete custom properties, you need to have the document opened in one of the applications in the office suite

PDF files certainly have properties that can be changed (such as title, description, etc), and while it is possible to open and amend a PDF document in Word, the resulting document would need to be saved as a separate document to the original, and the document formatting would likely be affected. Ordinarily, you would need third party software/library in order to change a PDF file's properties.

JPG files, however, do have quite a lot of metadata properties - called EXIF metadata. As it so happens, it IS possible to change those properties and add your own, but it's a very different method to the approach you've taken thus far. If it's something you'd like to explore, I can have a look for my code when I get home from work tonight.

 

[dellehurley]

Hi, I could be wrong, but my understanding is the Custom Document Properties are available only for Office documentation - ie., Word, Excel, Powerpoint, Access, etc. In order to add/change/delete custom properties, you need to have the document opened in one of the applications in the office suite

PDF files certainly have properties that can be changed (such as title, description, etc), and while it is possible to open and amend a PDF document in Word, the resulting document would need to be saved as a separate document to the original, and the document formatting would likely be affected. Ordinarily, you would need third party software/library in order to change a PDF file's properties.

JPG files, however, do have quite a lot of metadata properties - called EXIF metadata. As it so happens, it IS possible to change those properties and add your own, but it's a very different method to the approach you've taken thus far. If it's something you'd like to explore, I can have a look for my code when I get home from work tonight.

Hi Dan, Yes please. I have been researching the net for days looking for even the right words to search. I found something referring to this Exif last night but have not had a chance to look into it further. The PDF's are honestly not as big a deal as the JPGs. My highest priority is changing the TAGS. I would appreciate any help you can offer.
Dannielle

 

[dellehurley]

Hi Dan, Yes please. I have been researching the net for days looking for even the right words to search. I found something referring to this Exif last night but have not had a chance to look into it further. The PDF's are honestly not as big a deal as the JPGs. My highest priority is changing the TAGS. I would appreciate any help you can offer.
Dannielle

Hi Dan, Any luck? I'm sure you are busy so please don't feel that I'm being pushy, really double checking that you received my earlier reply.
Dannielle

 

[Dan_W]

Oh wow - I thought I had responded to this (I'm sure I did...?) and I was waiting for a response from you.. I'm so sorry.
What I (thought I) had responded with is: your explicit reference to TAGS reminded me of a file's extended file properties. These are relatively easier to access than a JPG's EXIF metadata - I suppose the easiest way of working out which ones they are is if you let me know how it is that you've been able to see this metadata - what have you been using to access it?

In either case, I will pull out a script for you know to check whether these the same items of metadata you're after and decide how to then delete/write over them.

 

[Dan_W]

Hello. Further to the above, I have adjusted my routine/and written some new code to test whether or not we are accessing the correct extended file properties. The code below should be pasted into a new VBA module in Excel. It comprises two routines - CheckFile and GetFileDetails. If you run the Checkfile routine, it will open a file picker window for you to select a file, it will then return the details of the following 11 properties of that file:

• "Date Modified", "Date Created", "Date Visited"
• "Event"
• "File Description", "Description", "Program Description"
• "Tags", "Title", "Subject", "Category", "Comments"

You will see that I have put Event, Description and Tags - the others are included for completeness on the off-chance that they also contain relevant data that you want to deal with. If it is accessing the correct data points, I will look into working out how best to change them for you - I note in your original code that you have a user form - do you intend to change the data through that?

Let me know how you go.

Sub CheckFile()
   
        Dim Filename As Variant, i As Long
       
        Filename = Application.GetOpenFileName
        If Filename = False Then Exit Sub
       
        Dim Results(11) As String
        Dim FieldNames As Variant
        Dim FieldNumbers As Variant
       
        FieldNames = Array("Date Modified", "Date Created", "Date Visited", "Event", "File Description", "Description", "Program Description", "Tags", "Title", "Subject", "Category", "Comments")
        FieldNumbers = Array(3, 4, 186, 231, 34, 187, 259, 18, 21, 22, 23, 24)
       
        Dim FinalResult As String * 60
        Dim FieldLabel As String * 20
        Dim counter As Long
        For counter = LBound(FieldNames) To UBound(FieldNames)
            FieldLabel = FieldNames(counter)
            FinalResult = Format(FieldNumbers(counter), "000") & vbTab & FieldLabel & vbTab & vbTab & GetFileDetails(Filename, CLng(FieldNumbers(counter)))
            Results(counter) = FinalResult
        Next
       
        MsgBox "Results: " & Filename & vbCr & vbCr & Join(Results, vbCr)
   
    End Sub

   
    Function GetFileDetails(ByVal FilePath As String, FieldNumber As Long) As Variant
   
      Dim Folder      As Object
      Dim FolderItem  As Object
      Dim Path        As Variant
      Dim Filename    As String
         
      If Dir(FilePath) = "" Then Exit Function
     
      With CreateObject("Scripting.FileSystemObject")
          Filename = .GetFileName(FilePath)
          Path = .GetParentFolderName(FilePath)
      End With
     
      Set Folder = CreateObject("Shell.Application").Namespace(Path & "\")
      If (Not Folder Is Nothing) Then
          Set FolderItem = Folder.ParseName(Filename)
          If (Not FolderItem Is Nothing) Then
              GetFileDetails = Folder.GetDetailsOf(FolderItem, FieldNumber)
          End If
      End If
     
      Set FolderItem = Nothing
      Set Folder = Nothing
   
    End Function

 

[dellehurley]

Hello. Further to the above, I have adjusted my routine/and written some new code to test whether or not we are accessing the correct extended file properties. The code below should be pasted into a new VBA module in Excel. It comprises two routines - CheckFile and GetFileDetails. If you run the Checkfile routine, it will open a file picker window for you to select a file, it will then return the details of the following 11 properties of that file:

• "Date Modified", "Date Created", "Date Visited"
• "Event"
• "File Description", "Description", "Program Description"
• "Tags", "Title", "Subject", "Category", "Comments"

You will see that I have put Event, Description and Tags - the others are included for completeness on the off-chance that they also contain relevant data that you want to deal with. If it is accessing the correct data points, I will look into working out how best to change them for you - I note in your original code that you have a user form - do you intend to change the data through that?

Let me know how you go.

Sub CheckFile()
 
        Dim Filename As Variant, i As Long
     
        Filename = Application.GetOpenFileName
        If Filename = False Then Exit Sub
     
        Dim Results(11) As String
        Dim FieldNames As Variant
        Dim FieldNumbers As Variant
     
        FieldNames = Array("Date Modified", "Date Created", "Date Visited", "Event", "File Description", "Description", "Program Description", "Tags", "Title", "Subject", "Category", "Comments")
        FieldNumbers = Array(3, 4, 186, 231, 34, 187, 259, 18, 21, 22, 23, 24)
     
        Dim FinalResult As String * 60
        Dim FieldLabel As String * 20
        Dim counter As Long
        For counter = LBound(FieldNames) To UBound(FieldNames)
            FieldLabel = FieldNames(counter)
            FinalResult = Format(FieldNumbers(counter), "000") & vbTab & FieldLabel & vbTab & vbTab & GetFileDetails(Filename, CLng(FieldNumbers(counter)))
            Results(counter) = FinalResult
        Next
     
        MsgBox "Results: " & Filename & vbCr & vbCr & Join(Results, vbCr)
 
    End Sub

 
    Function GetFileDetails(ByVal FilePath As String, FieldNumber As Long) As Variant
 
      Dim Folder      As Object
      Dim FolderItem  As Object
      Dim Path        As Variant
      Dim Filename    As String
       
      If Dir(FilePath) = "" Then Exit Function
   
      With CreateObject("Scripting.FileSystemObject")
          Filename = .GetFileName(FilePath)
          Path = .GetParentFolderName(FilePath)
      End With
   
      Set Folder = CreateObject("Shell.Application").Namespace(Path & "\")
      If (Not Folder Is Nothing) Then
          Set FolderItem = Folder.ParseName(Filename)
          If (Not FolderItem Is Nothing) Then
              GetFileDetails = Folder.GetDetailsOf(FolderItem, FieldNumber)
          End If
      End If
   
      Set FolderItem = Nothing
      Set Folder = Nothing
 
    End Function

Hi Dan,
I have done that before particularly, ie. think I have responded, several times I have wondered why I have not heard back from someone on messenger and my message has not been sent. No harm no foul.
As for your code it works perfectly.
The reason I am so keen to get tags going is because the filenames the jpg are assigned on there own don't refer to the subject at all. Recently I had a photo of a man and I could not identify him for the life of me. Luckily someone I work with recognised him. This is the flaw in my system however. If the record is deleted or corrupted by accident I can end up with a situation like this and this is a way of preventing the issue.

I have taken a screenshot showing what is required where and where the info is located. A copy of the userform and a few jpgs are in the link below. Ideally I would like the tags to be written when the userform is saved as all the required information is either linked or created in the userform.
The file to process opens up file explorer. Once a file is selected the details are entered, names are linked and the Tag info is created too. When the userform saves the file is renamed and moved into the folder, ie. the same folder as the excel file is saved in.
Userform and assoc Files
Thanks again for your help.
Dannielle

 

[dellehurley]

Hi Dan,
I have done that before particularly, ie. think I have responded, several times I have wondered why I have not heard back from someone on messenger and my message has not been sent. No harm no foul.
As for your code it works perfectly.
The reason I am so keen to get tags going is because the filenames the jpg are assigned on there own don't refer to the subject at all. Recently I had a photo of a man and I could not identify him for the life of me. Luckily someone I work with recognised him. This is the flaw in my system however. If the record is deleted or corrupted by accident I can end up with a situation like this and this is a way of preventing the issue.

I have taken a screenshot showing what is required where and where the info is located. A copy of the userform and a few jpgs are in the link below. Ideally I would like the tags to be written when the userform is saved as all the required information is either linked or created in the userform.
The file to process opens up file explorer. Once a file is selected the details are entered, names are linked and the Tag info is created too. When the userform saves the file is renamed and moved into the folder, ie. the same folder as the excel file is saved in.
Userform and assoc Files
Thanks again for your help.
Dannielle

Hi Dan,
I have done that before particularly, ie. think I have responded, several times I have wondered why I have not heard back from someone on messenger and my message has not been sent. No harm no foul.
As for your code it works perfectly.
The reason I am so keen to get tags going is because the filenames the jpg are assigned on there own don't refer to the subject at all. Recently I had a photo of a man and I could not identify him for the life of me. Luckily someone I work with recognised him. This is the flaw in my system however. If the record is deleted or corrupted by accident I can end up with a situation like this and this is a way of preventing the issue.

I have taken a screenshot showing what is required where and where the info is located. A copy of the userform and a few jpgs are in the link below. Ideally I would like the tags to be written when the userform is saved as all the required information is either linked or created in the userform.
The file to process opens up file explorer. Once a file is selected the details are entered, names are linked and the Tag info is created too. When the userform saves the file is renamed and moved into the folder, ie. the same folder as the excel file is saved in.
Userform and assoc Files
Thanks again for your help.
Dannielle

Just checking in.

 

[Dan_W]

Hello. My apologies - I had wanted to update you yesterday but it completely slipped my mind. I had a look at the files you uploaded. I originally had it in my mind that you were wanting to scrub the JPG files of metadata, but I just reread your original post and that's not what you said at all! I think I've got a handle on what it is you're trying to do, and I'm cautiously confident that I can help. I think the way to approach this after all is through EXIF metadata as discussed previously - the means of doing this though might either be a bit tricky or really easy. Do you happen to know what version of Office 365 (which is what you have according to you mini profile here) you have? It will either be 32bit office or 64bit office? You can find out by clicking:

1. the FILE tab, and then
2. ACCOUNT (down the bottom left hand side) and then
3. ABOUT EXCEL

On the top line at the end it will either say 64bit or 32bit - mine is 64.

What I might do is send you a short script for you to run a test first, to see if the method I'm thinking of using will work. Does that sound ok?

 

[dellehurley]

Hello. My apologies - I had wanted to update you yesterday but it completely slipped my mind. I had a look at the files you uploaded. I originally had it in my mind that you were wanting to scrub the JPG files of metadata, but I just reread your original post and that's not what you said at all! I think I've got a handle on what it is you're trying to do, and I'm cautiously confident that I can help. I think the way to approach this after all is through EXIF metadata as discussed previously - the means of doing this though might either be a bit tricky or really easy. Do you happen to know what version of Office 365 (which is what you have according to you mini profile here) you have? It will either be 32bit office or 64bit office? You can find out by clicking:

1. the FILE tab, and then
2. ACCOUNT (down the bottom left hand side) and then
3. ABOUT EXCEL

View attachment 60646
View attachment 60645
On the top line at the end it will either say 64bit or 32bit - mine is 64.

What I might do is send you a short script for you to run a test first, to see if the method I'm thinking of using will work. Does that sound ok?

Hi Dan,
I had a look and it is 32 bit. All sounds good. I can't find anything (or the right words to search)on Google to try and figure this out on my own so I appreciate it.
Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20118) 32-bit