Solved

VB. NET: Excel BuiltinProperties - DirectCast fails

Posted on 2004-04-30
12
1,613 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
  • 8
  • 4
12 Comments
 
LVL 5

Expert Comment

by:tgannetts
Comment Utility
.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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:MarkNethercott
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:MarkNethercott
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now