Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Read range excel without MFC in c++ (using ole2.h)

310 views
Skip to first unread message

Mark O

unread,
May 7, 2004, 7:43:12 AM5/7/04
to
Does anybody know how to obtain a range of values from Excel in a c++
console application array?
If so, would you please provide me with an example?


Thanks and regards , Mark

Volker Hilsheimer

unread,
May 7, 2004, 12:17:40 PM5/7/04
to
"Mark O" <onr...@wanadoo.nl> wrote in message
news:19f1f902.04050...@posting.google.com...

> Does anybody know how to obtain a range of values from Excel in a c++
> console application array?
> If so, would you please provide me with an example?

Untested/compiled Win32 pseudo-code using only ole2.h, omitting error
checks for brevity:

IDispatch *excelApp = 0;
CoCreateInstance(uuidof(Excel.Application), 0, CLSCTX_SERVER,
IID_IDispatch, (void**)&excelApp);

VARIANT result;
VariantInit(&result);

VARIANT args[8]; // up to eight parameters
DISPPARAMS params;
params.cArgs = 0;
params.cNamedArgs = 0;
params.rgdispidNamedArgs = 0;
params.rgvarg = args;

DISPID dispid;
OLECHAR *api = L"Workbooks";
excelApp->GetIDOfNames(IID_NULL, &api, 1, LOCALE_USER_DEFAULT, &dispid);
excelApp->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT,
DISPATCH_PROPERTYGET, &params, &result, 0, 0);
IDispatch *excelWorkbooks = result.pdispVal;

OLECHAR *api = L"Add";
excelWorkbooks->GetIDOfNames(IID_NULL, &api, 1, LOCALE_USER_DEFAULT,
&dispid);
excelWorkbooks->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT,
DISPATCH_METHOD, &params, &result, 0, 0);
excelWorkbooks->Release();
IDispatch *excelWorkbook = result.pdispVal;

OLECHAR *api = L"Range";
args[0].vt = VT_BSTR;
args[0].bstrVal = SysAllocStringLen(L"A1", 2);
args[1].vt = VT_BSTR;
args[1].bstrVal = SysAllocStringLen(L"C3", 2);
params.cArgs = 2;
excelWorkbook->GetIDOfNames(IID_NULL, &api, 1, LOCALE_USER_DEFAULT,
&dispid);
excelWorkbook->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT,
DISPATCH_PROPERTYGET, &params, &result, 0, 0);

SysFreeString(args[0].bstrVal);
SysFreeString(args[1].bstrVal);
excelWorkbook->Release();

IDispatch *excelRange = result.pdispVal;


Now you have a Range object, and you can call the "Value" property with
code simliar to the stuff above.

Obviously it will be easier to use a decent C++ framework that
encapsulates all that parameters stuff for you, e.g. ActiveQt

http://doc.trolltech.com/3.3/activeqt.html


When done, call Quit on Excel.Application and release the interface to
shut down Excel again.

Volker


Mark O

unread,
May 10, 2004, 8:16:36 AM5/10/04
to
Thanks Volker! Things are becoming increasingly clear to me. (I'm new
to c++)
I've also been searching for howto just open an existing Excel
document. Should be simple, but if you can tell me how to do that, I'd
be more than happy, cause I've been experimenting with it for hours
with no 'luck' yet

It should look something like this:

IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks,
L"Open(c:\\test.xls");
pXlBook = result.pdispVal;
}

but it just ddoesnt execute (it does compile, though). Hope you can
help me once again :)

Regards, Mark

Volker Hilsheimer

unread,
May 10, 2004, 1:17:51 PM5/10/04
to
Hello Mark,

The only thing I can tell you for sure is that the problem is in the
AutoWrap function :)

Now I guess the code for that is a bit longer than useful here, but note
that "Open" is not a property getter (so call as DISPATCH_METHOD rather
than DISPATCH_PROPERTYGET). Then I assume your AutoWrap function simply
parses the string, calls IDispatch::GetIdOfNames() for the part before the
opening parenthesis, turns every parameters into a VARIANT of type VT_BSTR
(not caring about syntax errors, your string literal is missing the
closing parenthesis), and calls IDispatch::Invoke() with the results. This
should work :)

Volker

PS: You should also initialize your pointer to 0.

"Mark O" <onr...@wanadoo.nl> wrote in message

news:19f1f902.04051...@posting.google.com...

Santhosh Pillai [MS]

unread,
May 10, 2004, 2:29:30 PM5/10/04
to
216686 HOWTO: Automate Excel From C++ Without Using MFC or #import
http://support.microsoft.com/?id=216686

"Mark O" <onr...@wanadoo.nl> wrote in message

news:19f1f902.04050...@posting.google.com...

Mark O

unread,
May 11, 2004, 9:04:50 AM5/11/04
to
This is the function as I'm using it now.

// Call Workbooks.Add() to get a new workbook...


IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);

AutoWrap(DISPATCH_METHOD, &result, pXlBooks,
L"Open(c:\\test.xls)",0);
pXlBook = result.pdispVal;
}

Still it doesnt understand the open method. Indeed, it seems to parse
the autowrap function call. What I need is to find the right syntax to
open the xls book in this manner. And it seems infindeable on the
internet. I'll keep looking for it, cause I can imagine it'd make lots
of people happy to find out about this - especially dummies like me :)

Errormessage I get is:

IDispatch::GetIdSOfNames("Open(c:\test.xls)"failed)w/err 0x80020006

Any help greatly appreciated :)
Regards Mark

Mark O

unread,
May 11, 2004, 9:43:51 AM5/11/04
to
Got it: This is the code to open an excel workbook. The Name of the
book should be passed as a separate variable:

// Call Workbooks.Add() to get a new workbook...
IDispatch *pXlBook;
{

VARIANT result;
VariantInit(&result);
VARIANT x;
x.vt = VT_BSTR;
x.bstrVal = ::SysAllocString(L"C:\\test.xls");
AutoWrap(DISPATCH_METHOD, &result, pXlBooks, L"open",1, x);
pXlBook = result.pdispVal;
SysFreeString(x.bstrVal);
}

Thanks for your help! Now I can continue reading ranges from my
workbook into an array.

Regards,
Mark

Mark O

unread,
Jun 3, 2004, 1:08:26 PM6/3/04
to
> Thanks for your help! Now I can continue reading ranges from my
> workbook into an array.
>
> Regards,
> Mark

Me again!

I'm trying to:

Read values from an existing Excel sheet, and put them in an array.
Now, I assume I have to put something into these lines (where the << are:

IDispatch *pXlRange;
{
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal = ::SysAllocString(L"A1:C8");

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
VariantClear(&parm);

for(int i=1; i<=15; i++) {
for(int j=1; j<=15; j++) {
VARIANT tmp;
tmp.vt = VT_I4;
tmp.lVal = i*j; <<<<<<<<<< here must the values of the range be put in..
// Add to safearray...
long indices[] = {i,j};

SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
pXlRange = result.pdispVal;
}

but I just cant figure out what that would be. Can you help me?

Regards,

Mark

Mark O

unread,
Jun 5, 2004, 8:26:37 AM6/5/04
to
I'm quite stuck, and not smart or educated enough to find out myself
:( I just cant find any example code on how to get an excel range into
an array, without using mfc. I really hope someone can help me.
HEEEELP, PLEASE? :)

Regards,

Mark

Alexander Nickolov

unread,
Jun 5, 2004, 9:54:55 PM6/5/04
to
Consider asking in the office groups.

--
=====================================
Alexander Nickolov
Microsoft MVP [VC], MCSD
email: agnic...@mvps.org
MVP VC FAQ: http://www.mvps.org/vcfaq
=====================================


"Mark O" <onr...@wanadoo.nl> wrote in message

news:19f1f902.04060...@posting.google.com...

Mark O

unread,
Jun 7, 2004, 5:10:24 PM6/7/04
to
Volker? How do I call the 'value' property?

Thanks much!

Alexander Nickolov

unread,
Jun 8, 2004, 12:40:43 AM6/8/04
to
Use DISPID_VALUE (=0) in your IDispatch::Invoke call.

--
=====================================
Alexander Nickolov
Microsoft MVP [VC], MCSD
email: agnic...@mvps.org
MVP VC FAQ: http://www.mvps.org/vcfaq
=====================================
"Mark O" <onr...@wanadoo.nl> wrote in message
news:19f1f902.04060...@posting.google.com...

Mark O

unread,
Jun 8, 2004, 8:35:08 AM6/8/04
to
Hi Alexander. Thanks for your comment!

Right now I did this (don't laugh :)):

for(int i=1; i<=15; i++) {
for(int j=1; j<=15; j++) {
VARIANT tmp;
tmp.vt = VT_I4;

tmp.lVal = DISPID_VALUE;


// Add to safearray...
long indices[] = {i,j};

This at least doesnt give me the errors I got before. But now, it
writes an array (safearray) with all 0's to the sheet. My intention is
for it to read the existing values from the sheet into the array and
write them back to the sheet again. This still is not what happens.
Hope you have another suggestion for my struggle ..

Greetings, Mark

Alexander Nickolov

unread,
Jun 9, 2004, 12:24:57 AM6/9/04
to
I'm sorry, I'm not familiar with the Office Automation model.
Again, ask in the office groups:

microsoft.public.office.developer.office.sdks
microsoft.public.excel.programming

--
=====================================
Alexander Nickolov
Microsoft MVP [VC], MCSD
email: agnic...@mvps.org
MVP VC FAQ: http://www.mvps.org/vcfaq
=====================================
"Mark O" <onr...@wanadoo.nl> wrote in message
news:19f1f902.04060...@posting.google.com...

Mark O

unread,
Jun 15, 2004, 10:47:09 AM6/15/04
to
Ok thanks Alexander. Posted in other newsgroup, but until now no
reply. I'm trying to make use of Volkers code to figure out the right
action to read a range from Excel into my array. I also read some KB
articles, but not one answers my question.
Volker? If you're not on holiday :) and have time and will to help me
just a little bit further, I'd more than very much appreciate that!

Regards,

Mark

0 new messages