"Does not support a collection" error when trying to write to Excel?

[Dave Calkins]

I'm attempting to use automation to interface with Excel. I used the VS2003
wizard to import MFC classes from the type library. I'm able to
successfully connect up with Excel, query for the version, create a new
workbook, and save it. The problem comes in when I try and write some
simple text to the document.

I've included a code snippet I put together to do some initial testing. The
code works except for the section between "BROKEN-START" and "BROKEN-STOP".
With that code un-commented I get a dialog indicating, "Does not support a
collection". In the code below, the Excel namespace is something I created
which includes all the generated classes from the type library (I just
declared the namespace around all the header inclusions).

Note that I get the same dialog if I replace the code between the -START
and -STOP with code which uses get_Worksheets() and then attempts to inquire
as to the size of the worksheets collection.

I'm sure there's something really basic I'm missing here. Any ideas?

===

#define VAR_STR(s) COleVariant(_T(s))
#define VAR_CSTR(s) COleVariant(s)
#define VAR_ENUM(e) COleVariant((long)e)
#define VAR_LONG(l) COleVariant((long)l)
#define VAR_EMPTY COleVariant()
#define VAR_PARAMNOTFOUND COleVariant((long)DISP_E_PARAMNOTFOUND,VT_ERROR)

void TestExcel()
{
COleException err;
LPDISPATCH pDispatch;

long lcid = GetUserDefaultLCID();

// automation objects
Excel::CApplication excelApp;
Excel::CWorkbooks workbooks;
Excel::CWorkbook workbook;
Excel::CWorksheet worksheet;
Excel::CRange range;

// get Excel application
if (excelApp.CreateDispatch(_T("Excel.Application"), &err))
{
// check version
CString ver = excelApp.get_Version();
if (_tcstod(ver,NULL) >= 11.0)
{
// create a new workbook with a single worksheet
workbooks = excelApp.get_Workbooks();
workbook = workbooks.Add(VAR_PARAMNOTFOUND);

////////////////////////////////////////////////////////////////
// BROKEN-START
////////////////////////////////////////////////////////////////
// the below causes a dialog to pop up which says,
// "Does not support a collection" ???
////////////////////////////////////////////////////////////////
// write some text into the document
worksheet = excelApp.get_ActiveSheet();
range = worksheet.get_Range(VAR_STR("A1"),VAR_STR("A1"));
range.put_Value2(lcid,VAR_STR("Hello World"));
////////////////////////////////////////////////////////////////
// BROKEN-STOP
////////////////////////////////////////////////////////////////

// save the new workbook
CFileDialog fd(FALSE,_T(".xls"),_T("myreport.xls"),0,
_T("Worksheet Files (*.xls)|*.xls|All Files (*.*)|*.*||"));
if (fd.DoModal() == IDOK)
{
workbook.SaveAs(VAR_CSTR(fd.GetPathName()),VAR_ENUM(Excel::xlWorkbookNormal),
VAR_EMPTY,VAR_EMPTY,VAR_EMPTY,VAR_EMPTY,(long)Excel::xlNoChange,
VAR_EMPTY,VAR_EMPTY,VAR_EMPTY,VAR_EMPTY,VAR_EMPTY);
}

AfxMessageBox(_T("done"));
}
else
{
AfxMessageBox(_T("Office 2003 (v11.0) or later must be installed."));
}
}
else
{
AfxMessageBox(_T("Office 2003 (v11.0) or later must be installed."));
}

// close the workbook
workbook.Close(VAR_LONG(0),VAR_EMPTY,VAR_EMPTY);

// ensure automation objects are all released
range.ReleaseDispatch();
worksheet.ReleaseDispatch();
workbook.ReleaseDispatch();
workbooks.ReleaseDispatch();
excelApp.ReleaseDispatch();
}

 

[Dave Calkins]

Made a little more progress. Studio generates a CRange class and a CRange0
class. I found that if I use CRange0 instead and then use the put_Item()
method of that class it works. i.e., the get_Range() method must be
returning a CRange0 not a CRange. Making the mods shown below now cause it
to work.

Perhaps the rule is that if it generates a CRange and CRange0, always use
the "0" one? ......

===

Excel::CRange0 range;

// .. snip ..

worksheet = excelApp.get_ActiveSheet();
range = worksheet.get_Range(VAR_STR("A1"),VAR_STR("A1"));
//range.put_Value2(lcid,VAR_STR("Hello World"));
range.ClearContents();
range.put_Item(VAR_LONG(1),VAR_LONG(1),VAR_STR("Hello world"));

 

[Dave Calkins]

I did some digging in the type library (used oleview then saved it as IDL).
For the Range interface, the IDL specifies both an interface and a
dispinterface, which is probably what caused studio to generate 2 classes.

The pattern I see is below. I've indicated how the interfaces are declared
in the IDL and which studio generated class I'm using which seems to work.

interface _Application => CApplication
interface Workbooks => CWorkbooks
interface _Workbook => CWorkbook
interface _Worksheet => CWorksheet

interface IRange (and) dispinterface Range => CRange0

So it has something to do with which interface is being declared in the IDL
wrt which generated class to use. I don't see anything in the generated
classes (CRange or CRange0) which would tell you which one to use other than
the simple rule of if you see multiple classes, be aware you'll have to do
some trial and error to figure out which ones to use.

Or perhaps this means that selecting ALL the interfaces in the type library
was not the correct way to use the studio wizard?

 

[Peter Huang [MSFT]]

Hi,

I am researching the issue, and I will update you with new information ASAP.

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang [MSFT]]

Hi David,

Based on my research, the ordinal number after the interface is added by
the Wizard, it just increase one by one, because the Wizard did not
understand the relation between the interfaces you are selected, so it
seems to be messy.
So far I did not found a good workaround, but commonly the principle is
just to select the interfaces you will use but not select all which will
help to eliminated the problem. To some extent, the Office COM Object Modal
is really a complex one.

Based on my experience, I would like to use the #import approach to import
the Excel lib, but not the MFC wizard.
Here is a link for your reference.
Placing images in Excel using automation
http://www.codeproject.com/com/ComExcelImages.asp?df=100&forumid=112901&exp=
0&select=936388

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Dave Calkins]

Just wanted to provide a little additional followup on this issue. After
attempting the hybrid approach discussed above, it turned out there were far
too many discrepencies between the signatures of the VS2005 and VS2003 code.

An alternate solution was used. We completely scrapped the generated code
and wrote our own class derived from COleDispatchDriver. Our derived class
allows dynamically getting and setting properties and invoking methods by
name. It performs the name lookup (and has a map which caches the name =>
dispid so future lookups just use the cache) and then uses
COleDispatchDriver to to the actual interaction.

The derived class has helper methods for getting properties which directly
store to regular types (as opposed to VARIANTS). It also has invoke methods
which are similar to the base class but which take all VARIANTs as args as
opposed to the mix and match used by the generated classes. This greatly
simplifies things and with a few macros, the variant passing is trivial.

Using this class, we're able to very cleanly dynamically call an OLE
Automation server. For now, this seems to be a lot better approach than
trying to use the generated classes.