Solved

VB. NET: Excel BuiltinProperties - DirectCast fails

Posted on 2004-04-30
12
1,694 Views
Last Modified: 2011-01-19
The following fails with;

An unhandled exception of type 'System.InvalidCastException' occurred in resultsoutput.dll
Additional information: Specified cast is not valid.

In general terms the code is constructed as follows;

ModuleA
Public  xlWb As Excel.Workbook

ModuleB
SubA
    Dim prps As Microsoft.Office.Core.DocumentProperties
    prps = DirectCast(xlWb.BuiltinDocumentProperties, Microsoft.Office.Core.DocumentProperties)

This is using;
Framework 1.1
Excel 2002 SP3
Reference to COM Microsoft Excel 10.0 Object Library (Version 1.4)
0
Comment
Question by:MarkNethercott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
12 Comments
 
LVL 5

Expert Comment

by:tgannetts
ID: 10966482
.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.
0
 

Author Comment

by:MarkNethercott
ID: 10968004
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.
0
 
LVL 5

Expert Comment

by:tgannetts
ID: 10984784
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.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:MarkNethercott
ID: 10985691
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.
0
 

Author Comment

by:MarkNethercott
ID: 10985796
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
0
 
LVL 5

Expert Comment

by:tgannetts
ID: 10986070
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.

0
 

Author Comment

by:MarkNethercott
ID: 10987760
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.

0
 

Author Comment

by:MarkNethercott
ID: 10987856
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
0
 

Author Comment

by:MarkNethercott
ID: 10987931
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.
0
 

Author Comment

by:MarkNethercott
ID: 10988129
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."
0
 
LVL 5

Accepted Solution

by:
tgannetts earned 500 total points
ID: 10988351
You need to use the following:

objwb.CustomDocumentProperties.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, Nothing, objwb.CustomDocumentProperties, New Object() {"Test", False, Office.MsoDocProperties.msoPropertyTypeString, "Hello", Nothing})

The function is as follows:

Add(ByVal name as String, ByVal LinktoContent as Boolean, ByVal Type as Office.MsoDocProperties, ByVal Value as Object, ByVal LinkSource as Object)

I have tested this in Excel 2000 and it seems to work.

Good luck!

Tom
0
 

Author Comment

by:MarkNethercott
ID: 10988549
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.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question