Automation of Excel97 in visualC++ : macro and new sheets problems

Automation of Excel97 in visualC++ : macro and new sheets problems

Post by David RO » Wed, 10 Sep 2003 00:46:32


Dear all,

I'm trying to automate Excel 97 in Visual C++ (using the article "HOWTO :
Create Automation Project Using MFC and a TypeLibrary").
It works not so bad, but I have encountered 2 major problems :

- I can't manage to run a macro.

I'm using the code :

ExcelApp.Run( COleVariant( "test.xls!Macro1" ), COleVariant( (short) NULL ),
COleVariant( (short) NULL ),... ) ;
The active document is "Classeur1.xls" but "test.xls" is opened in read-only
mode. ExcelApp is obtained with

ExcelApp.CreateDispatch("Excel.Application") and it's good (i've filled in
some sheets)



- I can't manage to add a new sheet

I tried to use the Worskheets.Add() function, but I don't know how to fill
in the two first arguments ("before" and "after") and even if it compiles it
doesn't work.

Thanks in advance to anyone who could help me.

David

--
David ROY
ENSICA - Promotion 56
XXXX@XXXXX.COM
06.11.40.60.97

SOLID DYNAMICS
53, rue Albert Thomas
42300 ROANNE
Tel : 04.77.70.21.11
Fax : 04.77.70.87.00

"Miez-vous des res de jeunesse, ils finissent toujours par se rliser."
Goethe
 
 
 

Automation of Excel97 in visualC++ : macro and new sheets problems

Post by Wolfgang.U » Wed, 10 Sep 2003 15:43:23

Hello,

to start a macro in XL97 I first open the workbook that
contains the macrocode (via Workbooks.open()). To make
life a litte easier I modified the wrapperclass of XL97:

VARIANT _Application::Run_0Paras(const VARIANT& Macro)
{
VARIANT result;
static BYTE parms[] = VTS_VARIANT;
InvokeHelper(0x103, DISPATCH_METHOD, VT_VARIANT,
(void*)&result, parms, &Macro);
return result;
}
VARIANT _Application::Run_1Paras(const VARIANT& Macro,
const VARIANT& Arg1)
{
VARIANT result;
static BYTE parms[] = VTS_VARIANT VTS_VARIANT;
InvokeHelper(0x103, DISPATCH_METHOD, VT_VARIANT,
(void*)&result, parms, &Macro, &Arg1);
return result;
}
Don't forget to update the *.h!!

To start the macro I simply call
Cxl8.Run_1Paras(COleVariant("mcName"),COleVariant
("Param1"));


Your second problem - try to pass the following as the
optional parameters "before" and "after" (if you want XL
to igore it):
COleVariant(static_cast <long> (DISP_E_PARAMNOTFOUND),
VT_ERROR)

Hope that helps
Wolfgang

article "HOWTO :
problems :
COleVariant( (short) NULL ),
opened in read-only
good (i've filled in
know how to fill
even if it compiles it
toujours par se rliser."

 
 
 

Automation of Excel97 in visualC++ : macro and new sheets problems

Post by David RO » Wed, 10 Sep 2003 16:42:50

Thanks a lot Wolfgang !

I was really convinced that I had tried to put covOptional ( COleVariant
covOptional((long) P_E_PARAMNOTFOUND, VT_ERROR); )
as argument in sheets.add() but it seems that I did not... So it works now.

But I can't manage to run a macro. The difference with your exemple is that
I try to run a macro which is in a book, in another book.
So I need to give the path of the macro in the context of the active sheet
(that's why I wrote "test.xls!Macro1" and not just "Macro1").
But I must confess that your modification of the function run() is very
useful !!!

David

"Wolfgang.Unger" < XXXX@XXXXX.COM > a rit dans le message de

Hello,

to start a macro in XL97 I first open the workbook that
contains the macrocode (via Workbooks.open()). To make
life a litte easier I modified the wrapperclass of XL97:

VARIANT _Application::Run_0Paras(const VARIANT& Macro)
{
VARIANT result;
static BYTE parms[] = VTS_VARIANT;
InvokeHelper(0x103, DISPATCH_METHOD, VT_VARIANT,
(void*)&result, parms, &Macro);
return result;
}
VARIANT _Application::Run_1Paras(const VARIANT& Macro,
const VARIANT& Arg1)
{
VARIANT result;
static BYTE parms[] = VTS_VARIANT VTS_VARIANT;
InvokeHelper(0x103, DISPATCH_METHOD, VT_VARIANT,
(void*)&result, parms, &Macro, &Arg1);
return result;
}
Don't forget to update the *.h!!

To start the macro I simply call
Cxl8.Run_1Paras(COleVariant("mcName"),COleVariant
("Param1"));


Your second problem - try to pass the following as the
optional parameters "before" and "after" (if you want XL
to igore it):
COleVariant(static_cast <long> (DISP_E_PARAMNOTFOUND),
VT_ERROR)

Hope that helps
Wolfgang

article "HOWTO :
problems :
COleVariant( (short) NULL ),
opened in read-only
good (i've filled in
know how to fill
even if it compiles it
toujours par se rliser."