[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VB. NET: Excel BuiltinProperties - DirectCast fails

Posted on 2004-04-30
12
Medium Priority
?
1,730 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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