Adding and Changing 'CustomDocumentProperties' from VBA on other f

[Guest]

Does anyone know the VBA code to add or change a 'CustomDocumentProperty' in
an Office document at the file level without opening the document?

Assume that the VBA code will be running in an Excel workbook.

 

[Tom Ogilvy]

MS provides a DLL to support this. Unfortunately this article has been
updated to reflect .Net, but the DLL should still work from VBA i would
think

http://support.microsoft.com/default.aspx?scid=kb;en-us;224351


Here is some information I posted in 1999

===============>
Leo,
I think you are trying too hard. In Excel97, I went into the VBE and into
Tools=>References and created a reference to the DSOFile.dll using the
browser.

Then the following code worked fine:

Sub TestDSOFILE()
Dim docProp As DSOleFile.DocumentProperties
Set docProp = PropertyReader.GetDocumentProperties( _
sFilename:="Y:\docs\Excel_quit.xls")
Debug.Print docProp.AppName
Debug.Print docProp.Author
Debug.Print docProp.Name
End Sub

It returned the following
Microsoft Excel
Thomas W. Ogilvy
Excel_quit.xls

Once you have the reference, you can use the object browser to see what
other properties are available.

HTH,
Tom Ogilvy
===============>
Of course it needs to be registered with regsvr32

in code
Shell "RegSvr32 ""C:\Winnt\system32\DSOFILE.dll"" /s "

depending on where you placed the file.

if you are willing to open it:

http://www.cpearson.com/excel/docprop.htm
at Chip Pearson's site.

 

[Guest]

Thanks for the reply, Tom.

I've downloaded the DLL - it looks like you have to be in an application
development environment, like C++, VB, etc. I'm not sure how this can be
used in Visual Basic for Applications (VBA).

Am I missing something?

Regards,
VBA Dabbler

 

[Tom Ogilvy]

After you run the setup, then you should be able to go to the VBE in Excel

In tools references, create a reference to it as shown in the article.

Then you should be able to run code like this:

Sub TestDSOFILE()
' Create an instance of the DSOFile Object...
Set m_oDocumentProps = New DSOFile.OleDocumentProperties
' Lists both Summary and Custom Properties...
Dim oSummProps As DSOFile.SummaryProperties
Dim oCustProp As DSOFile.CustomProperty
Dim sFile As String, sTmp As String
Dim fOpenReadOnly As Boolean

' Here is where we load the document properties for the file.
' Depending on the read-only option set in the Open dialog, we
' can open for editing or just read-only. If you pass False to
' read-only flag and the file cannot be edited (because of access)
' an error will occur. For simple case, we can pass the optional flag
' ask the component to switch to read-only if file is protected.


sFile = "C:\addresses.doc" '<== change to a file you want to look at

m_oDocumentProps.Open sFile, fOpenReadOnly,
dsoOptionOpenReadOnlyIfNoWriteAccess

' Get the SummaryProperties (these are built-in set)...
Set oSummProps = m_oDocumentProps.SummaryProperties

' We'll load a few of these properties into text boxes which we
' can change in this sample. Other properties can be changed
' as well, but not by this sample...
txtTitle = oSummProps.Title
txtAuthor = oSummProps.Author
txtComments = oSummProps.Comments

Debug.Print txtTitle
Debug.Print txtAuthor
Debug.Print txtComments

For Each oCustProp In m_oDocumentProps.CustomProperties
sTmp = oCustProp.Name & ": " & CStr(oCustProp.Value)
sTmp = sTmp & " [" & CustTypeName(oCustProp.Type) & "]"
Debug.Print sTmp
Next
' Close before exit...
m_oDocumentProps.Close
End Sub
'*********************************************************************
' Helper Functions
'*********************************************************************
Private Function CustTypeName(lType As Long) As String
' This function simply maps string names to the
' VARIANT type of a custom property.
Select Case lType
Case 1
CustTypeName = "String"
Case 2
CustTypeName = "Long"
Case 3
CustTypeName = "Double"
Case 4
CustTypeName = "Boolean"
Case 5
CustTypeName = "Date"
Case Else
CustTypeName = "Unknown"
End Select
End Function

 

[NickHK]

Tom has included some code already, but FYI, the VB files are just text
really, so you can copy/paste into VBA, with a few alterations.

NickHK

 

[Guest]

Tom,
Thanks so much for your help - this really simplified and sped-up my routine.
Regards,
VBA Dabbler

After you run the setup, then you should be able to go to the VBE in Excel

In tools references, create a reference to it as shown in the article.

Then you should be able to run code like this:

Sub TestDSOFILE()
' Create an instance of the DSOFile Object...
Set m_oDocumentProps = New DSOFile.OleDocumentProperties
' Lists both Summary and Custom Properties...
Dim oSummProps As DSOFile.SummaryProperties
Dim oCustProp As DSOFile.CustomProperty
Dim sFile As String, sTmp As String
Dim fOpenReadOnly As Boolean

' Here is where we load the document properties for the file.
' Depending on the read-only option set in the Open dialog, we
' can open for editing or just read-only. If you pass False to
' read-only flag and the file cannot be edited (because of access)
' an error will occur. For simple case, we can pass the optional flag
' ask the component to switch to read-only if file is protected.


sFile = "C:\addresses.doc" '<== change to a file you want to look at

m_oDocumentProps.Open sFile, fOpenReadOnly,
dsoOptionOpenReadOnlyIfNoWriteAccess

' Get the SummaryProperties (these are built-in set)...
Set oSummProps = m_oDocumentProps.SummaryProperties

' We'll load a few of these properties into text boxes which we
' can change in this sample. Other properties can be changed
' as well, but not by this sample...
txtTitle = oSummProps.Title
txtAuthor = oSummProps.Author
txtComments = oSummProps.Comments

Debug.Print txtTitle
Debug.Print txtAuthor
Debug.Print txtComments

For Each oCustProp In m_oDocumentProps.CustomProperties
sTmp = oCustProp.Name & ": " & CStr(oCustProp.Value)
sTmp = sTmp & " [" & CustTypeName(oCustProp.Type) & "]"
Debug.Print sTmp
Next
' Close before exit...
m_oDocumentProps.Close
End Sub
'*********************************************************************
' Helper Functions
'*********************************************************************
Private Function CustTypeName(lType As Long) As String
' This function simply maps string names to the
' VARIANT type of a custom property.
Select Case lType
Case 1
CustTypeName = "String"
Case 2
CustTypeName = "Long"
Case 3
CustTypeName = "Double"
Case 4
CustTypeName = "Boolean"
Case 5
CustTypeName = "Date"
Case Else
CustTypeName = "Unknown"
End Select
End Function


--
Regards,
Tom Ogilvy

Thanks for the reply, Tom.

I've downloaded the DLL - it looks like you have to be in an application
development environment, like C++, VB, etc. I'm not sure how this can be
used in Visual Basic for Applications (VBA).

Am I missing something?

Regards,
VBA Dabbler