Re: Automating Excel - C / C++ / MFC Discussion Boards

DKT__23-Aug-05 1:43

DKT__

23-Aug-05 1:43

Hi,

I am googling for about 5 hours now, to find a good tutorial about automating excel.

What I need:

I want to load data from a database into excel, make a chart out of it, and safe it as a html file.

I found some articles concerning this at the msdn. But none helps me out, cause its ALWAYS MFC. And I want to code in straight c++. Next problem is, it always needs special header files like excel.cpp and excel.h, which dont exist anywhere...

Is there any article about excel automation for normal c++ out there?

Word Automation was no problem, but it seems to be different from excel!

DKT

Re: Automating Excel

David Crow23-Aug-05 3:10

David Crow

23-Aug-05 3:10

Have you seen MSDN article Q178749? The MFC-specific parts of that article have nothing to do with the communication with Excel.

"One must learn from the bite of the fire to leave it alone." - Native American Proverb

Re: Automating Excel

Anonymous23-Aug-05 3:52

Anonymous

23-Aug-05 3:52

Ive seen it, yeah.

Ok, take this link for example:

http://support.microsoft.com/default.aspx?scid=kb;en-us;199691&Product=xlw2K[^]

I did all steps just like the MSDN tells me.

But I get thousands of errors.

First one:

_Application oApp; _Application isnt known.

I imported excel.exe, as I was told, I included the CApplication from the type library, which gives me more and more errors...

I dont understand that. I went the way MSDN told me, and it doesnt work at all.

This topic is hard, therefor I wanted to have an example code to learn from it. But this seems to be impossible, even for MSDN.

DKT

Re: Automating Excel

David Crow23-Aug-05 4:03

David Crow

23-Aug-05 4:03

After importing from the type library, did ClassWizard add a .h and a .cpp file to your project? If so, do they contain a _Application class? Did you #include the .h file correctly?

"One must learn from the bite of the fire to leave it alone." - Native American Proverb

Re: Automating Excel

Anonymous23-Aug-05 4:31

Anonymous

23-Aug-05 4:31

Yes, I get the h files after importing the library!

The problem is the next step:

Add the include statement for excel8.h after the include statement for stdafx.h at the top of the AutoProjectDlg.cpp program file:

#include "stdafx.h"
#include "excel8.h" // excel.h in the case of Excel 2002 and Excel 2003.

Theres NO excel.h (I am using 2002)

DKT

Re: Automating Excel

David Crow23-Aug-05 4:53

David Crow

23-Aug-05 4:53

Anonymous wrote:
Yes, I get the h files after importing the library!

Theres NO excel.h (I am using 2002)

These two statements contradict each other. If a .h file was created, just #include it, no matter what name it has.

"One must learn from the bite of the fire to leave it alone." - Native American Proverb

Re: Automating Excel

Anonymous23-Aug-05 21:08

Anonymous

23-Aug-05 21:08

What do you mean?

I got the specified h files like CApplication.h and stuff...
and yes, I included them, but as I said, get thousands more errors in those h files.

DKT

Re: Automating Excel

David Crow24-Aug-05 2:51

David Crow

24-Aug-05 2:51

Are you saying that ClassWizard created more than one .h and .cpp file? If so, I've not seen that happen before.

"One must learn from the bite of the fire to leave it alone." - Native American Proverb

Re: Automating Excel

Anonymous24-Aug-05 3:07

Anonymous

24-Aug-05 3:07

Hmm... I found a thing that might help me:

http://www.lordjoe.com/Java2Com/CPPCode.html[^]

But I have the problem with the following line:

hr = Invoke(pdispApplication, DISPATCH_METHOD, &vRet, NULL, NULL,
OLESTR("WorkBooks"), NULL);

if (FAILED(hr))
goto error;

I get: "Member not found".
This means WorkBooks is ok, but something is missing, had the same before for another method.

My question: I am searching for a reference to find the detailed properties for those methods. How to find out, how to invoke those methods correctly?

Damn, C++ and OLE is quiete hard...

DKT

Re: Automating Excel

David Crow24-Aug-05 3:19

David Crow

24-Aug-05 3:19

Anonymous wrote:
How to find out, how to invoke those methods correctly?

See MSDN article Q222101.

"One must learn from the bite of the fire to leave it alone." - Native American Proverb

Re: Automating Excel

Anonymous24-Aug-05 23:01

Anonymous

24-Aug-05 23:01

Ok, I could solve my problems till now. Thanks for that David!

But I still dont see what methods I can use and which not.

For example: I record a macro which runs another macro.

I see the vb code:

Application.Run

So, If I try to use Invoke with Run as OLESTR, it says "unknown". But other things like add or range work.

What is the methods name for run? Isnt there any paper for all possible methods for non-MFC based c++ OLE code?

MSDN says itself:

C/C++ Automation is much more difficult, but sometimes necessary to avoid overhead with MFC, or problems with #import. Basically, you work with such APIs as CoCreateInstance(), and COM interfaces such as IDispatch and IUnknown.

Yeah, it is difficult, but is it impossible?

Anyway, thanks alot for your help till now David, it helped alot.

DKT

Re: Automating Excel

David Crow25-Aug-05 4:31

David Crow

25-Aug-05 4:31

I have an Excel file with a macro named DisplayMsg. To open that file and run that macro, I used:

_Application    app;
Workbooks       books;
_Workbook       book;
COleVariant     vtOptional((long) DISP_E_PARAMNOTFOUND, VT_ERROR),
                vtTrue((short) TRUE),
                vtFalse((short) FALSE);
 
if (app.CreateDispatch("Excel.Application") == TRUE)
{
    books = app.GetWorkbooks();
    book = books.Open("C:\\Book2.xls",
        vtOptional, vtOptional, vtOptional, vtOptional, vtOptional, vtOptional,
        vtOptional, vtOptional, vtOptional, vtOptional, vtOptional, vtOptional);
 
    app.Run(COleVariant("DisplayMsg"),
        vtOptional, vtOptional, vtOptional, vtOptional, vtOptional, vtOptional,
        vtOptional, vtOptional, vtOptional, vtOptional, vtOptional, vtOptional,
        vtOptional, vtOptional, vtOptional, vtOptional, vtOptional, vtOptional,
        vtOptional, vtOptional, vtOptional, vtOptional, vtOptional, vtOptional,
        vtOptional, vtOptional, vtOptional, vtOptional, vtOptional, vtOptional);            
}

Does that help any?

"One must learn from the bite of the fire to leave it alone." - Native American Proverb

Re: Automating Excel

Anonymous25-Aug-05 4:37

Anonymous

25-Aug-05 4:37

No, sorry!

I am using the help files from Microsoft: INVHelp.cpp and INVHelp.h
Those work quiete well. It uses easier ways to use the invoke function.

Here, everything is getting invoked. Like:

V_VT(&v) = VT_I2;
V_I2(&v) = 20;

hr = Invoke(pdispRange, DISPATCH_PROPERTYPUT, NULL, NULL, NULL,
OLESTR("Value"), TEXT("v"), v);

The problem for me is now: I cant use Run or VBProject.

hr = Invoke(pdispWorksheet, DISPATCH_PROPERTYGET, &vRet, NULL, NULL,
OLESTR("VBProject"), NULL);

It gives me "exception error" in hr.

I just cant get it working. Normally this VBProject doesnt need more than the return value, right?

If Id just get this to work...

DKT

Re: Automating Excel

Anonymous25-Aug-05 4:55

Anonymous

25-Aug-05 4:55

Ok, here my code excerpts:

hr = CreateObject(OLESTR("Excel.Application"), &pdispApplication);

V_VT(&v) = VT_BOOL;
V_BOOL(&v) = TRUE;

hr = Invoke(pdispApplication, DISPATCH_PROPERTYPUT, NULL, NULL, NULL,
OLESTR("Visible"), TEXT("v"), v);

hr = Invoke(pdispApplication, DISPATCH_PROPERTYGET, &vRet, NULL, NULL,
OLESTR("WorkBooks"), NULL);

pdispWorkbooks = V_DISPATCH(&vRet);

V_VT(&v) = VT_I2;
V_I2(&v) = xlWorkSheet;

hr = Invoke (pdispWorkbooks, DISPATCH_METHOD, &vRet, NULL, NULL,
OLESTR("Add"), TEXT("v"), v);

hr = Invoke(pdispApplication, DISPATCH_PROPERTYGET, &vRet, NULL, NULL,
OLESTR("ActiveWorkBook"), NULL);

pdispWorksheet = V_DISPATCH(&vRet);

hr = Invoke(pdispWorksheet, DISPATCH_PROPERTYGET, &vRet, NULL, NULL,
OLESTR("VBProject"), NULL);

Everything works fine till the last invoke. Excel gets started, and a new workbook opened. ActiveWorkBook also works, but not the last invoke. I even cant call ThisWorkbook, same result: exception error...

DKT

Re: Automating Excel

David Crow25-Aug-05 4:58

David Crow

25-Aug-05 4:58

Anonymous wrote:
hr = Invoke(pdispWorksheet, DISPATCH_PROPERTYGET, &vRet, NULL, NULL,
OLESTR("VBProject"), NULL);

Are the first and sixth arguments correct?

"One must learn from the bite of the fire to leave it alone." - Native American Proverb

Re: Automating Excel

Anonymous25-Aug-05 20:35

Anonymous

25-Aug-05 20:35

I think so.

As I saw in the VBA "code", it is called like that:

ActiveWorkbook.VBProject.VBComponents.Import

What I did: I call "ActiveWorkbook" to get the active workbook. This gets stored in the pdispWorksheet, and is used for "VBProject". (I know this pdispWorksheet is confusing, but its just for testing).
I also tried to take the pdisp of my newly added Workbook directly, but it gets me the same result.

If I use pdispApplication or something like this, it shows me: Unknown method. If I change DISPATCH_PROPERTYGET to something else, I get: wrong member.
So, I thought it should be ok, how I am calling the method. But I dont see any logic why I get exceptions all the time. Theres not much more in this Invoke...

DKT

Re: Automating Excel

Anonymous25-Aug-05 22:08

Anonymous

25-Aug-05 22:08

What I just found out:

I also got exceptions when Invoking "Run" to run a given macro.
I just found out that it happens cause the macro didnt exist in the sheet.
I put a breakpoint before the run, and created a little macro with the correct name. Then I traced over the run, and wow, it works...

What does that mean for the VBProject? I mean, it doesnt take any parameters, does it?
It just holds a retVal which is from type VARIANT.

DKT

load image

dave2k23-Aug-05 1:06

dave2k

23-Aug-05 1:06

how can i get a BYTE* from a bmp file?

thankyou

Re: load image

toxcct23-Aug-05 1:09

toxcct

23-Aug-05 1:09

REPOST !!! Mad | :mad:

you didn't even way 1 hour !! Suspicious | :suss:

TOXCCT >>> GEII power
[toxcct][VisualCalc] Suspicious | :suss:

Re: load image

ThatsAlok23-Aug-05 1:54

ThatsAlok

23-Aug-05 1:54

dave2k wrote:
how can i get a BYTE* from a bmp file?

check out this api :- [CBitmap::GetBitmapBits]

"Opinions are neither right nor wrong. I cannot change your opinion. I can, however, change what influences your opinion." - David Crow

cheers,
Alok Gupta
VC Forum Q&A :- I/ IV

Combo box in a menu !!! Is it possible ?

UsmanMKhan23-Aug-05 1:01

UsmanMKhan

23-Aug-05 1:01

I have facing a problem. I have a tool bar on a window.
When the child window is resized the tool buttons are obscured which I did like. So solve the problem I used rebar control hosted my tool bars on this rebar when the window is resized now and hides a portion of the toolbar ,a chevron appears. when you click on the chevron a menu pop ups which contains the obscured buttons.
BUT THE PROBLEM is that some of my tool bars contain a combo boxes.
HOW SHOULD I AM I GOING TO MAKE THE MENU DISPLAY MY COMBO BOXES TOO?

ANY IDEA ,ANY ONE

Re: Combo box in a menu !!! Is it possible ?

toxcct23-Aug-05 1:09

toxcct

23-Aug-05 1:09

why wouldn't you make it a sub menu instead ?

TOXCCT >>> GEII power
[toxcct][VisualCalc]

I Agree with you but .....

UsmanMKhan23-Aug-05 2:25

UsmanMKhan

23-Aug-05 2:25

both of us share the same point of view but my does not seem to agree with us Frown | :(

.
A little while ago i got the idea that instead of using a menu I should create a dialog box where the menu should have appeared . Wink | ;)

But i will need your help in doing so.

Re: I Agree with you but .....

toxcct23-Aug-05 5:33

toxcct

23-Aug-05 5:33

hum, i'm not sure i understand what is that new point you want to achieve.

moreover, what is the relation with the first point you asked on the forum ?

TOXCCT >>> GEII power
[toxcct][VisualCalc]

Re: I Agree with you but .....

UsmanMKhan24-Aug-05 21:10

UsmanMKhan

24-Aug-05 21:10

What I want achieve is to show a combo in a menu box.
but creating a combox requires it to be created as child window of some other window. But since Menu is not derived from cwnd so we cant make a menu a parent of a combo box. I hope I am clear till this point.

What i am trying to do is not something very new. because in visual studio.net 2003 when you resize tool bars the combo boxes can now appear in the pop up menu that appears when you click the >> (chevron).

What I was trying to suggest was since we cant use the CMenu class now ,we should consider using a dialog box. This dialog log box will appear where the menu appears now when we click the >> button on a tool whose all buttons are not visible.
The dialog will act as a menu and we will make it act like as menu i.e. hightlighting and unhighlighting items when mouse moves over it and destroy it when it loses focus.
I hope I make some sense this time.
if anything is still obscure please ask

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.