Help Getting VC++ COM code to work with newer versions of Excel.

Hello,

I have a VC++ 6.0 application that extracts information from Excel workbooks. Microsoft changed something in Excel 2007, and now one small but important part of the app does not work in Excel 2007 and later.

I believe I have found the root cause, and I think it is more of a COM problem than an Excel problem.

The root cause seems to be a change in behavior in the following method:
Excel::NamePtr->RefersToRange:

This call is supposed to fail when the Name refers to a named formula, but it is supposed to return an Excel::Range pointer if the name refers to a cell or range.

When running on a machine with Excel 2007 or later, the function returns something, but it is not a Range object that responds to any of the Range method calls. I think Microsoft incorrectly changed COM interface in Excel 2007.

The app is compiled with the Excel 2003 OLB. It uses many calls and manipulations of other Excel objects and APIs this way, and all of them work across all versions of Excel from 2000-2013. It's only this one call to RefersToRange has different behavior under newer versions of Excel.

I believe that Microsoft changed the something in Excel 2007 and later that breaks compatibility with the earlier type libs.

The VC++6 application uses the following imports, and is compiled on a machine with Excel 2003 installed:


#import "C:\Program Files\Microsoft Office\Office\MSO9.DLL"
#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
#import "C:\Program Files\Microsoft Office\Office\EXCEL9.OLB" \
rename("DialogBox", "ExcelDialogBox") \
rename("RGB", "ExcelRGB") \
no_dual_interfaces


Here is a minimized example of the code to reproduce:

Please Login or Register  to view this content.

When running on a machine with Excel 2003 installed it produces the following expected messages:

*************************************
**************** Excel Version 9.0
******** 000 ****
******** 010 **** Name is:IN_RefNum
******** 100 ****
******** 110 **** R1C1 = =NamedSheet!R6C3
******** 200 ****
******** 210 ****
******** 300 ****
******** 310 **** pXLRange->GetAddress: R6C3
******** 400 ****
******** 410 ****
******** 420 **** WorkSheet Name is: NamedSheet

But when running on a machine with Excel 2007 or later installed it produces the following messages:

*************************************
**************** Excel Version 12.0
******** 000 ****
******** 010 **** Name is:IN_RefNum
******** 100 ****
******** 110 **** R1C1 = =NamedSheet!R6C3
******** 200 ****
******** 210 ****
******** 300 ****
Exception in 300-310 Getting pXLRange->GetAddress.
******** 400 ****
Exception in 400-420 Getting worksheet: UNINITIALIZED

I’d appreciate any help the community can provide. Ultimately I need to get this working for Excel 2003 and newer. I tried a lot of other things:

• I tried importing the Excel 2007 type library but got hundreds of naming conflicts. I may have done this incorrectly. Pointers on whether this should work and how to do it correctly would be appreciated.
• I tried to identify what exceptions were being thrown, but I could not, and was only able to catch the generic (…) catch block.
• The call to pXLName->RefersToRange returns something, but I was unable to determine what. I presume it is returning a new type of Range that is not supported by the Excel 2003 type lib. Pointers on how to identify what is being returned and how to call its methods might help progress.

Thanks in advance for any help you can give.