Thanks Rob for the input.
You were right, in the end I had to use reflection to contort my way through
the COM interface.
It took a while to figure out & I relied heavily on the help of Tom Annetts
@ Experts-Exchange.
The full thread can be found at;
http://www.experts-exchange.com/Prog..._20973995.html
I've included the final function here for completeness. I've only tested it
under XP SP3 & Excel 2002, where it works fine;
Sub SetProperty(ByV al 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
'isPropCustom 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.Offic e.Core.MsoDocPr operties.msoPro pertyTypeBoolea n
Case vbDate
PType = Microsoft.Offic e.Core.MsoDocPr operties.msoPro pertyTypeDate
Case vbDouble, vbLong, vbSingle, vbCurrency
PType = Microsoft.Offic e.Core.MsoDocPr operties.msoPro pertyTypeFloat
Case vbInteger
PType = Microsoft.Offic e.Core.MsoDocPr operties.msoPro pertyTypeNumber
Case vbString
PType = Microsoft.Offic e.Core.MsoDocPr operties.msoPro pertyTypeString
Case Else
PType = Microsoft.Offic e.Core.MsoDocPr operties.msoPro pertyTypeString
End Select
If isPropCustom Then
xlWB.CustomDocu mentProperties. GetType().Invok eMember("Add",
BindingFlags.In vokeMethod, _
Nothing, xlWB.CustomDocu mentProperties, New Object() {PName,
isLinkToContent , PType, PValue, LinkSource})
Else
result = xlWB.BuiltinDoc umentProperties .GetType().Invo keMember("Item" ,
BindingFlags.Ge tProperty, _
Nothing, xlWB.BuiltinDoc umentProperties , New Object() {PName})
result.GetType( ).InvokeMember( "Value", BindingFlags.Se tProperty,
Nothing, result, New Object() {PValue})
End If
End Sub
"Rob Teixeira [MVP]" <RobTeixeira@@m sn.com> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..
Yes, you're absolutely right. That's what I get for posting while
multi-tasking *sigh*
OK, this seems like a similar issue to a co-worker had.
Try this Mark - Use reflection on the object reference you do have to get
all implemented interfaces.
Also use Reflection to get the GuidAttribute from each (including the
GuidAttribute on the object ref's class).
Next, check to see that these are in fact the registered interfaces for
Excel in the system registry (based on the GUIDs).
I believe the problem may in fact be that you are trying to cast to a
newer version of the interface, which an older version of Excel might not
provide (or another such scenario). It would also help to know what version of the
PIAs you are using vs. what version of Office (and which PIAs have you
installed on that system).
-Rob Teixeira [MVP]
"Jay B. Harlow [MVP - Outlook]" <Ja************ @msn.com> wrote in message
news:e7******** ******@TK2MSFTN GP09.phx.gbl... Rob,
DirectCast will call QueryInterface to find the COM interface!
I will be very curious if CType works, where DirectCast fails.
Hope this helps
Jay
"Rob Teixeira [MVP]" <RobTeixeira@@m sn.com> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. .. Try CType instead of DirectCast.
In this case, you're dealing with COM wrappers, and I have the nagging
feeling you're using a newer interface and trying to cast directly to
an older one. CType should call QueryInterface under the hood, so that if the interface exists, it will get you a proper reference.
Hope that helps,
-Rob Teixeira [MVP]
"Mark Nethercott" <Ma************ *@iFabre.com> wrote in message
news:up******** *****@TK2MSFTNG P09.phx.gbl...
> I get the following failure when trying to access the builtin properties; >
> An unhandled exception of type 'System.Invalid CastException'
occurred in > resultsoutput.d ll
> 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.Offic e.Core.Document Properties
> prps = DirectCast(xlWb .BuiltinDocumen tProperties,
> Microsoft.Offic e.Core.Document Properties)
>
> This is using;
> Framework 1.1
> Excel 2002 SP3
> Reference to COM Microsoft Excel 10.0 Object Library (Version 1.4)
>
> I've been following the article by Ken Getz
>
(http://msdn.microsoft.com/library/de...-us/odc_vsto20 > 03_ta/html/ExcelObj.asp) which suggests the code should work. 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 this was a bug in the 2002
> COM or I've missed something in my code.
>
>
>
