VB. NET: Excel BuiltinProperties - DirectCast fails

.Net recognizes the BuiltInDocumentProperties collection as a System._ComObject, and you will not be able to cast it to type Microsoft.Office.DocumentProperties. You will need to use the following code:

Dim prps as Object
prps = xlWb.BuiltinDocumentProperties

Tom.

Thanks Tom.  I've tried your suggestion & it still fails.  With option strict on, the compiler doesn't allow late binding.

I've been following I've been following the article by Ken Getz

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp

which suggests the code should work as I've constructed it .

Ken's article refers to Excel 2003, but all the objects he references are also in the Microsoft Excel 10 COM object. I wondered whether my errors were caused by a bug in the 2002 COM or I've missed something in my code.

I think you will need to use Reflection to access the properties:

The following iterates through the BuiltInDocumentProperties collection and displays the name and value of each property in a MessageBox.

Dim objwb As Excel.Workbook
Dim objapp As Excel.Application
Dim result As Object
Dim val As String
Dim name As String
Dim count As Integer
Dim i As Integer

objapp = New Excel.Application()
objapp.Visible = True

objwb = objapp.Workbooks.Open("c:\Test.xls")

'Get properties count
count = Int32.Parse(objwb.BuiltinDocumentProperties.GetType().InvokeMember("Count", BindingFlags.GetProperty, Nothing, objwb.BuiltinDocumentProperties, New Object() {}).ToString)

For i = 1 To count '(Count property is not zero-based index)
 
     'Get Document Property at specified index
     result = objwb.BuiltinDocumentProperties.GetType().InvokeMember("Item", BindingFlags.GetProperty, Nothing, objwb.BuiltinDocumentProperties, New Object() {i})

     'Get Property Name
     name = result.GetType().InvokeMember("Name", BindingFlags.GetProperty, Nothing, result, New Object() {}).ToString

     Try
          'Get value for Property (some properties will throw an exception if you try to access their property)
          val = result.GetType().InvokeMember("", BindingFlags.GetProperty, Nothing, result, New Object() {}).ToString
     Catch ex As Exception
          val = "None"
     End Try

     'Show Property name and Value
     MsgBox(name.ToString & ": " & val.ToString)

Next

If you want to access specific Properties by name then replace the 'result = objwb.BuiltinDocumentProperties.GetType().InvokeMember("Item", BindingFlags.GetProperty, Nothing, objwb.BuiltinDocumentProperties, New Object() {i})' line with something like the following:

result = objwb.BuiltinDocumentProperties.GetType().InvokeMember("Item", BindingFlags.GetProperty, Nothing, objwb.BuiltinDocumentProperties, New Object() {"Author"})

Hope this helps.

Tom.

Thanks Tom for all your work, this is really useful.  

I'm still finding my way through 'Reflection' and am struggling to take your code to the next level where I can set a value for a property.

Here's my attempt so far;
The property I'm trying to set can have a variety of types, which is why I evaluate 'TheType'.  Because I know the properties I'm trying to set in advance, I can make sure I pass a property value of the correct type.  

Some properties are in the Buitlin set whilst others are in the custom set.

Calling routine ...

    SetProperty(xlWb, "Comment", CommentString, False)
    SetProperty(xlWb, "Company", "Company", False)
    SetProperty(xlWb, "Client", Customer, True)
    SetProperty(xlWb, "Date Completed", Now(), True)
    SetProperty(xlWb, "Language", "English", True)
    SetProperty(xlWb, "Owner", "ThisCompany", True)
    SetProperty(xlWb, "Status", "Release", True)
    SetProperty(xlWb, "Source", "Analysis Engine: " & GlobalVariableSet.analysisEngineNo, True)
    SetProperty(xlWb, "Document Number", "1", True)
...

  Sub SetProperty(ByVal xlWB As Excel.Workbook, ByVal PName As String, ByVal PValue As Object, _
    ByVal isPropCustom As Boolean)

    'This function sets the value of a property.
    'The property is added if it does not exist.
    '
    'The parameters passed to the function are as follows:
    '
    'WorkbookName       A string containing the name of the workbook whose property is to be set.  
    '                   If missing, the ActiveWorkbook is used.
    'PName              A string containing the name of the property
    'PValue             A variant containing the value of the property
    'PropCustom         A boolean indicating whether the property is a Custom Document Property.

    Dim TheType As Long
    Dim result As Object
    Dim count As Integer
    Dim name As String

    If isPropCustom Then
      count = Int32.Parse(xlWB.CustomDocumentProperties.GetType().InvokeMember("Count", BindingFlags.GetProperty, _
        Nothing, xlWB.CustomDocumentProperties, New Object() {}).ToString)
    Else
      count = Int32.Parse(xlWB.BuiltinDocumentProperties.GetType().InvokeMember("Count", BindingFlags.GetProperty, _
        Nothing, xlWB.BuiltinDocumentProperties, New Object() {}).ToString)
    End If

    Select Case VarType(PValue)
      Case vbBoolean
        TheType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeBoolean
      Case vbDate
        TheType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeDate
      Case vbDouble, vbLong, vbSingle, vbCurrency
        TheType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeFloat
      Case vbInteger
        TheType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeNumber
      Case vbString
        TheType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString
      Case Else
        TheType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString
    End Select

    'Set Property
    'xlWB.BuiltinDocumentProperties.GetType().InvokeMember("Item", BindingFlags.SetProperty, _
      Nothing, xlWB.BuiltinDocumentProperties, New Object() {PName}) = PValue


  End Sub

You're on the right lines:

To set the value of the Property you do need to use the SetProperty Binding flag, but you need to reference the DocumentProperty item in the DocumentProperties collection first and then set its Value property.

So:

result = xlWB.BuiltinDocumentProperties.GetType().InvokeMember("Item", BindingFlags.GetProperty, _
      Nothing, xlWB.BuiltinDocumentProperties, New Object() {PName})

This returns the data type DocumentProperty, which you can then access its methods and properties, so the next line of code sets the property value:

result.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, result, New Object() {pvalue})

(You don't need to specify "Value", as it is the default property - you can just pass a "")

If the invocation fails, then an exception will be thrown.

Tom.

This works perfectly for the BuiltinDocumentProperties, but I can't get it to work for the CustomDocumentProperties.

If I use your method to count the number of CustomDocumentProperties, I get a zero return (count=0).  This suggest to me that although the custom properties are listed if you look (using 'Properties/Custom' in the application) to get them defined I'll need to add them in as new.

I've managed to determine the properties and methods for the CustomDocumentProperties using

    Dim minfos() As MemberInfo = GetType(Excel.CustomProperties).GetMembers
    For Each mi As MemberInfo In minfos
      Console.WriteLine("{0} - {1}", mi.Name, mi.MemberType)
    Next

This shows up an Add method

The first bit I'm stuck on is how do I
a) find what the correct syntax is for the add method
b) implement it using the 'Reflection' method.  

I expect that the add method will want (Name, Type and Value) and that I'll need to pass it an array.  
So, where you've used InvokeMember("Value",  ... New Object() {pvalue}) above, (if I've started to understand 'Reflection') I'd expect to change this to;

InvokeMember("Add",  ... New Object() {PName, PType, PValue}

Though I'd need to find out what sort of Type declaration the method is expecting - I don't know whether 'TheType' I've declared above should work.

With thansks to Francesco Balena, I've found the following

    Dim mi As MethodInfo = GetType(Excel.CustomProperties).GetMethod("Add")
    Console.Write(mi.Name & "(")
    For Each pi As ParameterInfo In mi.GetParameters
      If pi.Position > 0 Then Console.Write(", ")
      Dim direction As String = "ByVal"
      If pi.IsOut Then direction = "ByRef"
      Console.Write("{0} {1} as {2}", direction, pi.Name, pi.ParameterType.FullName)
    Next
    Console.WriteLine(")")

To find the parameters required in the 'Add' method

This says the 'Add' method is = Add(ByVal Name as System.String, ByVal Value as System.Object)
But I can't figure out how to add it in.

I think I've finally distilled it out to use the 'InvokeMethod' as ...

      xlWB.CustomDocumentProperties.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, _
        Nothing, xlWB.CustomDocumentProperties, New Object() {PName, PValue})

but the command still fails with;

"An unhandled exception of type 'System.Reflection.TargetInvocationException' occurred in mscorlib.dll

Additional information: Exception has been thrown by the target of an invocation."

Many thanks -

For future reference, the final function came out as;

  Sub SetProperty(ByVal xlWB As Excel.Workbook, ByVal PName As String, ByVal PValue As Object, _
    ByVal isPropCustom As Boolean)

    'This function sets the value of a property.
    '
    'The parameters passed to the function are as follows:
    '
    'xlWB               A reference to the the workbook whose property is to be set.  
    'PName              A string containing the name of the property
    'PValue             A variant containing the value of the property
    'PropCustom         A boolean indicating whether the property is a Custom Document Property.

    Dim result As Object
    Dim PType As Long
    Dim isLinkToContent As Boolean = False
    Dim LinkSource As Object = Nothing

    Select Case VarType(PValue)
      Case vbBoolean
        PType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeBoolean
      Case vbDate
        PType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeDate
      Case vbDouble, vbLong, vbSingle, vbCurrency
        PType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeFloat
      Case vbInteger
        PType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeNumber
      Case vbString
        PType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString
      Case Else
        PType = Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString
    End Select

    If isPropCustom Then
      xlWB.CustomDocumentProperties.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, _
        Nothing, xlWB.CustomDocumentProperties, New Object() {PName, isLinkToContent, PType, PValue, LinkSource})
    Else
      result = xlWB.BuiltinDocumentProperties.GetType().InvokeMember("Item", BindingFlags.GetProperty, _
        Nothing, xlWB.BuiltinDocumentProperties, New Object() {PName})
      result.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, result, New Object() {PValue})
    End If

  End Sub

As an aside, I was down a bit of a dead end as my little bit of code (above) to get all the parameters of the 'Add' method didn't return all the parameters I needed.