Add-in: type mismatch COM exception while accessing Excel 2003

[Natalie]

Hello,

I get a type mismatch COM exception wenn I try to access some of the data in
Excel 2003.

While this code works fine:

Dim state As Excel.XlCalculationState
state = m_xlApp.CalculationState


Following code will cause a type mismatch exception:

Dim calc As Excel.XlCalculation
calc = m_xlApp.Calculation


The debugger shows following error (sorry, in German):
"Typkonflikt. (Ausnahme von HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))"
StackTrace"
Microsoft.Office.Interop.Excel.ApplicationClass.get_Calculation()"

I' m programming in VB.NET using Visual Studio 2005 with .NET 2.0.
My add-in is referencing the Excel PIA (Version 11) in the GAC.

I don't know if this detail is important: my add-in loads in MS-Project
2003, not Excel, and exports data from Project 2003 to Excel 2003.

Any help is greatly appreciated.

Natalie

 

[Cindy M.]

Hi =?Utf-8?B?TmF0YWxpZQ==?=,

Dim calc As Excel.XlCalculation
calc = m_xlApp.Calculation


The debugger shows following error (sorry, in German):
"Typkonflikt. (Ausnahme von HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))"
StackTrace"
Microsoft.Office.Interop.Excel.ApplicationClass.get_Calculation()"

Possibly, the PIA is looking for an Integer (VBA Long) data type, here.
In VBA all enums return Long values. Sometimes the PIAs don't allow you
to type an enum as the Enum type, sometimes they do.

Try Dim calc as Integer and see if that makes any difference. If not,
type it as an object then see what type the object contains.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Natalie]

Possibly, the PIA is looking for an Integer (VBA Long) data type, here.
In VBA all enums return Long values. Sometimes the PIAs don't allow you
to type an enum as the Enum type, sometimes they do.

Try Dim calc as Integer and see if that makes any difference. If not,
type it as an object then see what type the object contains.

Hello Cindy,

Thank you for your answer. I tried:

Dim calc As Integer
calc = xlApp.Calculation

and

Dim calc As Object
calc = xlApp.Calculation

In both cases, I get a System.Runtime.InteropServices.COMException (type
mismatch).

If I try to watch "xlApp.Calculation" using the debugger, the debugger also
says "type mismatch" instead of showing the value of "xlApp.Calculation".

The error can be replicated by:
(1) Creating a shared-addin
(2) adding following code to the OnConnection method:

Dim xlApp As Excel.Application = Nothing
Try
xlApp = New Excel.Application
Dim calc As Excel.XlCalculation
calc = xlApp.Calculation
Catch ex As Exception
MsgBox(ex.ToString)
End Try

If Not xlApp Is Nothing Then xlApp.Quit()


Any help is greatly appreciated!

Thanks
Natalie

 

[Natalie]

I found the solution to my problem.
I was expecting "Calculation" to be available during the whole lifetime of
the Excel instance. But it's not.

When creating a new Excel instance:

Dim xlApp As Excel.Application = New Excel.Application

The Excel attribute "Calculation" is not initialised yet. If you try to
access it, you get a type mismatch COM exception:

Dim calc As Excel.XlCalculation = xlApp.Calculation ' -> type mismatch
exception

The value of "Calculation" is only available if at least one workbook is
open. e.g:

' start Excel
Dim xlApp As Excel.Application = New Excel.Application
' create workbook
Dim xlWbk As Excel.Workbook = xlApp.Workbooks.Add()
' get value
Dim calc As Excel.XlCalculation = xlApp.Calculation

Actually, I would have expected the value "Nothing" instead of a "type
mismatch" exception.

To make it short, if you get a type mismatch COM exception, the reason may
be that the attribute you are trying to fetch has not been initialised yet.

Best regards
Natalie