excel automation

[act_x]

Can someone direct me to a good tutorial/ description as to how to use Excel in a VC++ app . I need to basically write to an excel file but use the Excel API .

[will1383]

Obviously, you will need to use OLE to accomplish this. I can't really give you alot of information other than check out MSDN or look on code project for OLE interfaces. Hopefully that will help. Sorry I can't help more. I haven't done any of this in a while, and I used perl when I did. Good Luck!

[David Crow]

First you'll need to import from Excel's type library. After that, use the classes that are provided. Here a small snippet that gets two values from the user, plugs them into two cells and puts their sum into a third:

\_Application    app;
Workbooks       books;
\_Workbook       book;
Worksheets      sheets;
\_Worksheet      sheet;
Range           range;
COleVariant     vtOptional((long) DISP\_E\_PARAMNOTFOUND, VT\_ERROR),
                vtTrue((short) TRUE),
                vtFalse((short) FALSE);
CString         strText;


if (app.CreateDispatch("Excel.Application") == TRUE)
{
    app.SetVisible(TRUE);

    books = app.GetWorkbooks();

    book = books.Add(vtOptional);

    sheets = book.GetSheets();

    sheet = sheets.GetItem(COleVariant((short) 1));

    range = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
    m\_ebCellA1.GetWindowText(strText);
    range.SetValue(COleVariant(strText));
    
    range = sheet.GetRange(COleVariant("A2"), COleVariant("A2"));
    m\_ebCellA2.GetWindowText(strText);
    range.SetValue(COleVariant(strText));

    range = sheet.GetRange(COleVariant("A4"), COleVariant("A4"));
    range.SetFormula(COleVariant("=A1 + A2"));
}

[Malcolm Smart]

David - you'll think I'm following you around the newsgroups...but it proves I've searched before asking the question.... Is the Excel API documented. I've got about as far as I can from the tutorials, ie opening a worksheet, updating cells, saving etc. I want to be able to move cells, pivot tables, cut'n'paste, insert formulaes, basically automate Excel from my app. Cheers Angel ************************************************************ The sooner you fall behind, the longer you have to catch up.

[David Crow]

Angel1058 wrote: Is the Excel API documented. Not to my knowledge. I use trial and error, looking through the .h file, and using VB. Those three together can usually tell me how a particular method works.

---

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

[Malcolm Smart]

Thanks all for the time taken (again!). I managed to get this far cribbing code from here and there. I am virtually there in what I want to do - it's very basic... A1 : D4 - a 4x4 grid. I can update any value no problem. When all cells are populated, I need to drop the first column, and move the remaining 3 columns along. The VBA for this is... Range("B1:D4").Select Range("B1:D4").Cut Desitination:=Range("A1:C4") Range("A1:C4").Select My interpretation (using the API) is lpDisp = sheet.GetRange(COleVariant("B1") , COleVariant("D4") range.AttachDispatch(lpDisp); range.Select(); range.Cut(COleVariant("A1:C4")); and I get 'Cut method of Range class failed'. Any chance of you spoon feeding me this last bit please? Cheers ********************************************* The sooner you fall behind, the longer you have to catch up.