Custom file description keywords: read/write in VBA via API

Custom file description keywords: read/write in VBA via API

• Erik_DD

  Hi Lee,
  Here's the engineer's response:
  
  

  If you mean that you want to use VBA for Excel 2003 (Excel 11) to read custom properties in an Office document, you need to use Office document object model. It provides some collection or property for this purpose.

   

  I have listed for Excel, Word, Powerpoint.

  CustomProperties Collection [Excel 2003 VBA Language Reference]
  http://msdn.microsoft.com/library/default.asp url=/library/en-us/vbaxl11/html/xlobjCustomProperties_HV03080858.asp

   

  CustomProperties Collection (Word)
  http://msdn.microsoft.com/library/default.asp url=/library/en-us/vbawd11/html/woobjCustomProperties_HV03084108.asp

   

  CustomDocumentProperties Property (Powerpoint)
  http://msdn.microsoft.com/library/default.asp url=/library/en-us/vbawd11/html/woobjCustomProperties_HV03084108.asp

   

   

  For example, the following VBA code gets/sets custom property of a Powerpoint document.
  'edit "complete" custom property value
  Application.ActivePresentation.CustomDocumentProperties _
      .Add Name:="Complete", LinkToContent:=False, _
      Type:=msoPropertyTypeBoolean, Value:=False

   

  'read "complete" custom property value
  With Application.ActivePresentation
      If .CustomDocumentProperties("complete") = False Then Debug.Print "false"
  End With

   

   

  Here is VBA code in Excel.

  Sub CheckCustomProperties()

   

      Dim wksSheet1 As Worksheet

   

      Set wksSheet1 = Application.ActiveSheet

   

      ' Add metadata to worksheet.
      wksSheet1.CustomProperties.Add _
          Name:="Market", Value:="Nasdaq"

   

      ' Display metadata.
      With wksSheet1.CustomProperties.Item(1)
          MsgBox .Name & vbTab & .Value
      End With

   

  End Sub

  
  
  -brenda (ISV Buddy Team)
  
  

• Magnan

  We passed your question on to one of our international support engineers and he wanted to share the following with you:
  
  According to your partner’s problem, I’ve found the following source code in Visual Basic for him, it will be very easy for him to translate it into VBA. If your partner does not know how to translate it into VBA, please tell me what VBA he is using, for ex, VBA for Word, or VBA for Excel, or even VBA in Project, etc…. After knowing the specific VBA version, I can help to translate the code into VBA if he wants.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

   

  The file package attached contains the source code to access the file summary both in VB6 code and VB.Net code, an ActiveX control that named DsoFile.dll should be registered so that we can access it, please follow the instructions in the following link to do that:

   

  Dsofile.dll lets you edit Office document properties without Office in Visual Basic .NET 2003 and in Visual Basic .NET 2002

  http://support.microsoft.com/ id=224351
  
  
  Please reply here if you need any additional assistance.
  thanks!
  -brenda (ISV Buddy Team)
  
  --
  This posting is provided "AS IS" with no warranties, and confers no rights.

  
  
  

• n8 MSFT

  Hello,
  
  I was hoping to find the translation form the VB6 or VB7 code for the dsofile.dll samples, but the translation was never posted. Can you ask whemever you were talking to (one of your international support engineers) to post a sample translation to VBA for Excel (Excel 11.0).
  
  speciffically, I'm interested in the custom properties functionality and I only need to read the information (no writing required).
  
  TIA
  Lee
  

• Rob Griffin

  I have tried adding this to my project and works fine. However the default location of the file is c:\dsofile\. If i copy the file to the system folder c:\winnt\system32 where other libraries are placed, I could not access the file. That is, the libray doesn't get added in the References box. Should I meddle with the DSO source code
  
  

• Ky Nam

  Per one of our internal support engineers:
  
  

  As to the partner’s reply, I’ve done the some research and following is my solution:< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  Actually, it is not because of the difference between Office file and Non-Office file that cause the “Can not read keyword property” issue. It is because of that the JPEG format has its own storage for the keywords and title fields. You can just do a test, create a .txt file which is not an Office file, and you can also modify the keywords field via the tool I send to you last time. On the other hand, JPEG has its own format that can contain the summary information instead of the NTFS file system, so we can not read it out by the DsoFile, or you can create a GIF file, it simply doesn’t have any summary information fields at all. So regarding some special file format like JPEG or MP3 that can contain the summary file information itself, the only way we can do is to develop the “Summary Information Reader” case by case.

  For instance, in JPEG format, we use EXIF tags for summary information storage, so if you want to access the summary filed of a JPEG file, you have to use the standard EXIF way, every EXIF tag has a Tag ID, as defined, the following is the Tag IDs for common properties:

  9c9b = Title

  9c9c = Comment

  9c9d = Author

  9c9e = Keywords

  9c9f = Subject

  And as a test, applying the above Tag IDs in the following sample VB.Net code, you can get the right summary info for the JPEG format:

  Accessing Extra Image Information in JPEG Files

  http://msdn.microsoft.com/library/en-us/dnhcvb04/html/vb04b15.asp

  Or in VB, you can use a EXIF utility JHead to fetch such information, it is also mentioned in the article.
  
  
  I hope this helps!
  -brenda (ISV Buddy Team)

  
  
  

• kdubious

  Thanks for the reply.       I am using VBA 6.3, under Windows XP Pro, 5.1.2600.
  
  Actually, your suggested method only addresses part of what I am hoping to do.
  I believe your suggested method address reading file properties of OFFICE files, but I was also hoping for a method that would be able to deal with other file types (JPEG, MP3, etc.)
  
  I have tried manually setting properties (such as 'keyword') on JPEGs, and when I open them with the sample applications based on DSOFILE.DLL, they are displayed, but the keyword field is shown as blank, whereas when I display the same data using Windows Explorer, the values I wrote into the 'keyword' field is shown properly.
  
  Should DSOFILE.DLL be able to read/write non-office files also
  
                best regards,
                                Dog-san
  

• stombiztalker

  Is there a complete list of metadata tags that will be added to Exif JPEG image files  In a previous message identified Tag IDs for Title (9c9b), Comment (9c9c ), Author (9c9d ), Keywords (9c9e), and Subject(9c9f ) common properties

   < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  I have found 11 metadata tags that are unknown to the Exif 2.21 defined metadata:

  1) Tag ID 4097 contains what happens to be the Image Width

  2) Tag ID 4098 contains what happens to be the Image Height 

  3) Tag ID 18246 or 0x4746 that through experimentation contains a Rating 

  4) Tag ID 18247 or 0x4747 contained the following Unicode :

  <Labels>

   

  <Word title="KeywordRoot">

   

  <Word title="airport">

  </Word>

   

  <Word title="Hawaii">

  </Word>

  </Word>

  <Word title="PeopleRoot">

  </Word>

  <Word title="LocationRoot">

  </Word>

  <Word title="EventRoot">

  </Word>

  <Word title="SyncRoot">

  </Word>

  </Labels>

   

  5) Tag 40091 or 0x9C9B that was identified as Title and it contains a unicode stream that contains  '100-0003_IMG'

  6) Tag 40094 or 0x9C9E that was identified as Keywords and it contains a unicode stream that contains 'Hawaii, airport'

  7-9)Tags 41985(0xA401),41986(0xA402),and 41987(0xA403) all have a value of zero

  10) Tag 41988 (0xA404) is a rational with a value of 2272/2272 or 1.0

  11) Tag 41990 (0xA406) has a value of 0

  
  
  

• DarkFader

  Here's the response I got from one of our support engineers:
  
  

  As far as I know, there is a specification for EXIF field reference from external internet as following, however, the EXIF fields are not defined by Microsoft and some applications may add custom unknown field for internal usage, so I have to say there may not be a perfect complete list. You may take it as a reference at http://www.hugsan.com/EXIFutils/Documentation/EXIFutils-FieldRef.pdf
  
  
  thanks,
  -brenda (ISV Buddy Team)

  
  
  
• Custom file description keywords: read/write in VBA via API