How to set my Excel DocumentProperty object variable (which is in a ByRef function) to a Word BuiltinDocumentProperties collection item?

Question

I had a similar problem as the user in this question.

Right now I cannot get my function variable proDocName which is type "DocumentProperty" to be set to wdDoc.BuiltinDocumentProperties.Item(1) in my Excel VBA below. It throws a type mismatch error.

The Excel VBA subroutine should first open an instance of word and then open a Word Document file.

Then I pass ByRef the file (I call it wdDoc in my code) to the function where I then try and grab the document properties' values and names which I want to write to my excel sheet using the function.

I've tested the following code in Word VBA which works fine:

Sub test()
Dim wdApp As Word.Application
Dim wdDocPro As DocumentProperty

Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument
Set wdDocPro = wdDoc.BuiltInDocumentProperties.Item(1) ' I get a type mismatch on this line

For Each wdDocPro In wdDoc.BuiltInDocumentProperties
MsgBox (wdDocPro.Name & " , " & wdDocPro.Value)

Next wdDocPro

End Sub

This test allowed me to see the "wdDoc"s document properties so I know it must be a problem with either 1.) the Excel versus Word object model for DocumentProperty or 2.) the function is somehow losing access to the Word.Document BuiltinDocumentProperties collection

This code in excel vba has issues when I get to the line where I set wdDocPro to the BuiltinDocumentProperties item.

Public Sub GetCurrentFolderConstants()
Dim DocVariables() As String
Dim wdApp as Word.Application
Dim wdDoc as Word.Document

Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument

DocVariables = DocVarGrabbing(wdDoc)
'Do stuff

wdDoc.Close True
'Do stuff with DocVariables
wdApp.Quit SaveChanges:=False
End Sub



'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function DocVarGrabbing(ByRef wdDoc As Word.Document) As String()

Dim wdDocPro As DocumentProperty 'Problem variable

'Set wdApp = GetObject(, "Word.Application")
'Set wdDoc = wdApp.ActiveDocument
'MsgBox wdDoc.Name

If wdDoc.BuiltinDocumentProperties.Count > 0 Then
    Set wdDocPro = wdDoc.BuiltinDocumentProperties.Item(1)'I get a type mismatch at this line
    For Each wdDocPro In ActiveDocument.BuiltinDocumentProperties
    'Do stuff with Document properties Names and Values and assign to DocVarArray (not needed for debugging)
    Next wdDocPro
End If

DocVarGrabbing = DocVarArray
Set wdDocPro = Nothing

End Function

Answer 1

The DocumentProperty type in this topic should be, in principle, of type Office.DocumentProperty - and DocumentProperties would be an Office.DocumentProperties object instance (the VSTO documentation seems to agree), and the Object Browser only finds a single class by that name, in the Office type library - so this isn't about Excel vs Word: neither define any DocumentProperties or DocumentProperty class.

That's one problem. The next thing is that a TypeOf type check apparently (surprisingly, actually) fails when you're out-of-process (e.g. inspecting a Word document from within an Excel workbook's VBA project, or inspecting an Excel workbook's properties from within a Word document's VBA project):

Public Function GetWordDocProps(ByVal doc As Word.Document) As Variant
    Dim properties As Variant 'Office.DocumentProperties
    Set properties = doc.BuiltinDocumentProperties

    Dim prop As Variant 'Office.DocumentProperty
    Set prop = properties.Item(1)

    If Not TypeOf properties Is Office.DocumentProperties Then Debug.Print TypeName(properties) 'prints "DocumentProperties"
    If Not TypeOf prop Is Office.DocumentProperty Then Debug.Print TypeName(prop) 'prints "DocumentProperty"

    '...        
End Function

When you ignore the types and treat everything as Variant, everything "just works".. but late-bound, so watch out for typos and expect error 438 if you try to invoke a member that doesn't exist.

Answer 2

As Mathieu stated in his answer, the key is to declare the properties variables as Variants.

This loop works using your example:

If you plan on looping through all properties you'll have to trap for value errors when they're not defined.

Dim wdApp       As Word.Application
Dim wdDoc       As Word.Document
Dim wdProp      As Variant
Dim wdProps     As Variant
  
Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument

Set wdProps = wdDoc.BuiltInDocumentProperties
wdProp = wdDoc.BuiltInDocumentProperties.Item(1)

Debug.Print wdProps.Count

On Error Resume Next

For Each wdProp In wdProps
    Debug.Print wdProp.Name
    Debug.Print wdProp.Value
    If Err.Number <> 0 Then
        MsgBox "ERROR: No Value For Property: " & wdProp.Name
        Err.Clear
    End If
Next wdProp