excel 2003 and c++ API documentation?

[danAutodesk]

I have been looking for documentation - any documentation or better yet, real
code examples regarding the c++ objects and methods for excel 2003. My
progress has been based on reverse engineering and guessing so is quite slow.
Help!

My purpose is exceptionally simple, to read (only, not write or format) data
from excel workbooks/worksheets.

I have a c++ MFC DLL project and have created excel classes from the type
library (actually .../EXCEL.EXE). The trouble really starts there - the
imported classes won't compile without some hand editing (Is that expected?).

After some "fixing," I can start the app with the following code:
if(!app.CreateDispatch(L"Excel.Application"))
{
//...todo - add error handling and cleanup
return;
}
else
{
//Make the app visible and verify the name - just for kicks!
COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);
app.put_Visible(TRUE);
app.put_UserControl(TRUE);
CString appName = app.get_Name();
...
}

But that's where I'm stuck. I can't open an existing workbook because I
don't know what to supply for the workbook methods to open an existing
workbook. Brining up any of the dialogs (such as app._FindFile()) control is
transferred to the app (away from my DLL) and is not returned until I dismiss
the dialog manually. Needless to say, that's definitely not useful....

Here's the code I'm currently playing with:

COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);
ExcelWorkbooks excelWBs = app.get_Workbooks();
ExcelWorkbook excelWB;
appName = app.get__Default();
ExcelParameter excelParam = excelWBs.get__Default(COleVariant((short)2));
excelWBs.Add(_T(".\\ExcelExample.xls"),covOptional, (ExcelParameter
**)&excelParam);


I've gotten this far by reverse engineering and poking around on Google --
VERY FRUSTRATING to spend several hours to get 10 lines of code for something
so trivially simple.

The VB and C# examples are plentiful and I can make those work easily. But
for many reasons this has to be a C++ project.

If could point me to any c++ code examples or documentation I would be
greatly relieved! ;-)

Thanks in advance.

dan

 

[Tom Ogilvy]

go to
http://support.microsoft.com

go to advanced search and search on C++ and Excel

 

[danAutodesk]

Thanks for your pointer. Alas it turns out at least a few of these articles
are the same ones I was able to Google. I can't get past the application
initialization. As such, I have a few more questions. I appologize in
advance for the length of this message (brevity is scarce in code
descriptions! ;-).

---
1. When I add any Excel object through the class / type lib wizard, a
header is generated for the selected object. That part works fine. At the
top of each header is the following code:
#import "C:\\Program Files\\Microsoft Office\\OFFICE11\\EXCEL.EXE"
no_namespace

When I #include this header in my project, the compiler doesn't like the
#import; warns of duplicate macro definitions and automatic exclusions. More
importantly, the compiler throws errors becasue of redefinitions in the
generated excel.tlh file.

---

2. Some imported Excel objects (such as, but definitely not limited to
Workbooks.h) that contain method signatures suchs as the ones below:
Application get_Application()
{
Application result;
...
XlCreator get_Creator()

These method signatures can't possibly work since the return types don't
exist. It's easy enough to fix these signatures so things compile.
Moreover, the code *seems* to run, but I don't know what is going on under
the covers at all.

---

There are several other issues like these that make me wonder whether I'm
dealing with bugs, or whether c++ developers are just supposed to get through
this with Excel.

I understand c++ developers are a little more wiling to endure difficulty.
That's why I was hoping there was some documentation on these APIs. Bumping
around in the dark is a losing proposition.

Finally, all the examples posted on the side you referenced look like they
were made with older versions of Excel. The object signatures are no longer
relevant. For example one example indicates: oBook =
oBooks.Add(covOptional); but the signature for this method in the generated
class (Excel 2003) is: (Add)(LPCTSTR Name, VARIANT& iDataType, Parameter * *
RHS)

There are no optional parameters apparently, and with no documentation on
type values, Parameter or the RHS macro, it's very difficult to know where to
go from here.

Again, I apologize for the length of this post. If you've gotten this far,
I thank you very much, and thank you even more for any and all assistance you
can provide.

dan

 

[Excel11 typelib gen''ing wrong functions]

Dan -- i'm running into the same thing you did. namely the examples i find on
MSDN are (possibly) for an older version of excel, therefore do not work for
Excel 2003. i've tried to follow your code snippets, but choke on the
following line...

ExcelParameter excelParam = oWorkbooks.get__Default(COleVariant((short)2));

i have

#import "C:\\Program Files\\Common Files\\Microsoft
Shared\\OFFICE11\\MSO.DLL" \
rename("RGB", "ExclRGB") \
rename("DocumentProperties", "ExclDocumentProperties") \
rename("SearchPath", "ExclSearchPath")
#import "C:\\Program Files\\Common Files\\Microsoft
Shared\\VBA\\VBA6\\VBE6EXT.OLB"
#import "C:\\Program Files\\Microsoft Office\\OFFICE11\\EXCEL.EXE" \
rename("DialogBox", "ExclDialogBox") \
rename("RGB", "ExclRGB") \
rename("CopyFile", "ExclCopyFile") \
rename("ReplaceText", "ExclReplaceText") \
rename("Font", "ExcelFont") \
rename("Parameter", "ExcelParameter") \
rename("DocumentProperties", "ExcelDocumentProperties") \
rename("ExitWindows", "ExcelExitWindows") \
rename("Rectangle", "ExcelRectangle") \
rename("Arc", "ExcelArc") \
rename("Picture", "ExcelPicture")

(note the "rename" for Parameter to ExcelParameter). however when i run my
code, it fails with a -2147352567 in oleDisp2.cpp on the following line...

SCODE sc = m_lpDispatch->Invoke(dwDispID, IID_NULL, 0, wFlags, &dispparams,
pvarResult, &excepInfo, &nArgErr);

what did you do differently that i'm not seeing?? tia!

 

[danAutodesk]

Hi -

I finally gave up; wasn't able to get it working and had to move on... for
now anyway. But eventually (and unfortunately) I will have to
programmatically read from an Excel spreadsheet.

One difference in your code is you've used rename(...) quite a lot. If I
can get some time to investigate that, I will do so and respond to this
thread.

But it feels like we may "barking up the wrong tree," so to speak. The fact
that there is no current documentation, and only 2 people who've even tried
this does not sound promising.

Maybe there is some more direct db connect method. In the fullness of time,
I'll look into that as well.

dan

 

[Excel11 typelib gen''''ing wrong functio]

actually, the app i wrote did exactly that...but i wrote it in the late 90's
with a down-levelled version of Excel. "they" made a few changes...and
documentation wasn't one of them.

if/when i get this working, i'll post something here. thanks!

 

[Excel11 typelib gen''''ing wrong functio]

well, i got it to add a workbook...AND generate an error in doing so (but at
least it's there). i overrode WorkBooks.Add(p1, p2, p3, p4) with

STDMETHOD(Add)() // added 7/19/06 dhm
{
HRESULT result;
InvokeHelper(0xb5, DISPATCH_METHOD, VT_HRESULT, (void*)&result, NULL);
return result;
}

so now i call: Workbooks.Add(); and a new Workbook appears. it also
generates an exception saying "bad variable type".

hey, it's a start.

 

[BillJ]

solved! there seems to be a bug in Visual Studio 2005. the generated header
files from a typelib are wrong.

workaround: use an older version of Visual Studio (i used .Net) and generate
the header files. i had no problems after that