Open Excel in VC++ and write data

I have a program which retrieves data from a SQL server. I want to write this data to an excel sheet and draw graphs using this data.
As of now I wrote the output of my program in csv format, so after I run the program it creates an excel file.
But how can I make my program open an excel sheet, write data to it, generate the graphs.
Basically my output should be an excel file with data and graphs in it.
Any kind of help is appreciated.
Thanks.

The place to start is
http://msdn.microsoft.com/library/de...sofficedev.asp

Try the tutorial examples at the MSDN site.

Next try searching in the c++ forums using Excel or automation as search terms.

Thanks a lot. That was helpful.
Now I have another problem. ....
I could open an excel application, create a new workbook and write data, (Strings and integers) to it. But I wanted to know how I could draw graphs with this data.
I recorded a macro in excel and I have the VB code to do what I want, but please tell me how I should write this VB code in C++
Thanks.

For an example of creating an xy chart:
http://www.codeguru.com/forum/showth...light=Chart%2A

Tom,

Thanks for your reply, but I am not using MFC's to automate excel. I wrote the Automation code in straight C++ with help from Microsoft Knowledgebase. Can you please guide me on how to draw charts with this.....
I could not find any help in the MS knowledgebase.
Appreciate your help.

I'm attaching a bare bones ( sphaghetti code without comments )example of creating an xy chart based on the example of automation without using the type library on MSDN.

I created anexcel file d:\junk.xls that had x data in a1-a7 and
y data in b1-b7. You will have to do so too if you try this program.

I first tried to use the chart wizard but eventually gave up and created the plot directly.

Tom,

Thankyou very much. That was a great help I could build bar and pie charts for my data with your help. But where do I get more information about the code. Which is the right place to read about what each statement is doing.

Again Thanks a bunch.
:)

Each time I do something in Automation the first thing I do is record a VBA macro and then translate it into C++.
I look up the functions that VBA used and the VBA help to get descriptions of the functions and the constants needed. The object inspector of the VBA editor (F2) is also a valuable resource. You can look up the classes and their members.

To find the methods and the arguments specifically for C, I look in the excel9.olb type library using OLEView.

That was helpful. I figured that out by looking at your code, but I thought may be there was a fast rule to do this.
I have another question, I tried recording macros in VB, but it does not seem to record all the things I do. Like for example, when I recorded a macro for drawing the graphs, I set the values to be seen on the top of the bar, but I could not see the code for that in the macro.

Quote:

---

Originally posted by gurupot
That was helpful. I figured that out by looking at your code, but I thought may be there was a fast rule to do this.
I have another question, I tried recording macros in VB, but it does not seem to record all the things I do. Like for example, when I recorded a macro for drawing the graphs, I set the values to be seen on the top of the bar, but I could not see the code for that in the macro.

---

Yes, I've seen that happen with macros. I don't know a way around it.

I appreciate your help earlier.
I could successfully write data and graphs to excel, but when I try to save it, its giving me an errror. The strange thing is, it worked first and after a few days it showed me this error. Here is my code, please let me know if you can help.

//Get the active workbook
IDispatch *pXlActiveWorkBook;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveWorkbook", 0);
pXlActiveWorkBook = result.pdispVal;
}

//Save the work book.
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"C:\\output.xls");
VARIANT fformat;
fformat.vt = VT_I4;
fformat.lVal= -4143;
AutoWrap(DISPATCH_METHOD, &result, pXlActiveWorkBook, L"SaveAs", 2, fname, fformat);
}

I tried out SaveAs and it caused an error for me too.

I then dropped the format as -4143 is the default anyways and it worked.

AutoWrap(DISPATCH_METHOD, &result, pXlActiveWorkBook, L"SaveAs", 1, fname);

Hello, i want to ask if anybody knows the way to use in C++ the DISPATCH_METHOD with AUTOWRAP for the function "ChartWizard" with it's possible to put the tittle in a excel's chart.

It would be something like that

AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard",...)

but i don't know what's the variables and how to specify the tittle, x column tittle, y column tittle.

I will be very grateful with any kind of help

I should be able to cook up an example about 12 hours from now.

Thankyou.

Just like before I couldn't get ChartWizard to work.
In the example I attached above, I was able to do it by creating the chart manually.

Sorry.

So there's no way to modify the chart charachteristics? the tittles, spaces between the grapchic lines.., if you know any way to do it i'll thank you very much.

And I can use with no compilation and execution problems the next line:

"AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard",1,var);"

where var is obtained steps before with the next sentences (data cells range):

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
var.vt = VT_DISPATCH;
var.pdispVal = result.pdispVal;

But theres no result, and as i can execute the autowrap with "chartwizard" I supose that there must be a way to use it.., with more variables or any thing, but if it's posible to execute the "ChartWizard" Dispatch_Method, I think that there's a way to do it.

In this long and tedious example I open a save worksheet called "junk.xls" which already has my x and y data in columns A and B. I create the chart using the chart wizard and then set the chart type, title, and grid line spacing on the x axis.
This worked for me in Visual C++ 6.0 using Excel 2000.

Code:

---

  // Initialize COM for this thread...
    CoInitialize(NULL);
   
    // Get CLSID for our server...
    CLSID clsid;
    HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
   
    if (FAILED(hr))
        {
        ::MessageBox(NULL, "CLSIDFromProgID() failed", "Error", 0x10010);
        return -1;
        }
   
    // Start server and get IDispatch...
    IDispatch *pXlApp;
    hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
    if (FAILED(hr))
        {
        ::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);
        return -2;
        }
   
    // Make it visible (i.e. app.visible = 1)
        {
        VARIANT x;
        x.vt = VT_I4;
        x.lVal = 1;
        AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
        }
       
// Get Workbooks collection
IDispatch *pXlBooks;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
}

IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"c:\\junk.xls\0");
AutoWrap(DISPATCH_METHOD, &result, pXlBooks, L"Open", 1, fname);
pXlBook = result.pdispVal;
}

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

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
pXlSheets = result.pdispVal;
}

IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_I4;
itemn.lVal = 1;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1, itemn);
pXlSheet = result.pdispVal;
}

VARIANT darange;

IDispatch *pXlRange;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"A1:B7");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
darange.vt = VT_DISPATCH;
darange.pdispVal = result.pdispVal;
pXlRange = result.pdispVal;
}

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlRange, L"Select", 0);
}

IDispatch *pXlChartObjects;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"ChartObjects", 0);
pXlChartObjects = result.pdispVal;
}

IDispatch *pXlChartObject;
{
VARIANT result;
VariantInit(&result);
VARIANT left, top, width, height;
left.vt = VT_R8;
left.dblVal = 100.;
top.vt = VT_R8;
top.dblVal = 200.;
width.vt = VT_R8;
width.dblVal = 350.;
height.vt = VT_R8;
height.dblVal = 250.;

AutoWrap(DISPATCH_METHOD, &result, pXlChartObjects, L"ADD", 4, left, top, width, height);
pXlChartObject = result.pdispVal;
}

IDispatch *pXlChart;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlChartObject, L"Chart", 0);
pXlChart = result.pdispVal;
}

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard", 1, darange);
}


{
VARIANT result;
VariantInit(&result);
VARIANT hastitle;
hastitle.vt=VT_BOOL;
hastitle.boolVal=TRUE;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlChart, L"HasTitle", 1,hastitle);

}







IDispatch *pXlChartTitle;
        {
    VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET,&result,pXlChart,L"ChartTitle",0);
        pXlChartTitle=result.pdispVal;
        }
IDispatch *pXlChars;
        {
    VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_METHOD,&result,pXlChartTitle,L"Characters",0);
        pXlChars=result.pdispVal;
        }

{
VARIANT result;
        VariantInit(&result);
VARIANT thetitle;
thetitle.vt=VT_BSTR;
thetitle.bstrVal = ::SysAllocString(L"This is my title");
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlChars, L"Text", 1,thetitle);
}

//set to xl_XYScatterNoMarkers
{
VARIANT result;
VariantInit(&result);
VARIANT type;
type.vt = VT_I4;
type.lVal = 75;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlChart, L"ChartType", 1, type);
}
//get x axis
IDispatch *pXlXAxis;
        {
    VARIANT result;
        VariantInit(&result);
    VARIANT axisind;
        axisind.vt=VT_I4;
        axisind.lVal=1;
        AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"Axes", 1, axisind);
        pXlXAxis=result.pdispVal;
       

        }
        {
        VARIANT result;
        VariantInit(&result);
    VARIANT hasgrid;
        hasgrid.vt=VT_BOOL;
        hasgrid.boolVal=TRUE;
    AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlXAxis, L"HasMajorGridlines", 1, hasgrid);

        }
//set x axis grid spacing to 2 units
        {
        VARIANT result;
        VariantInit(&result);
    VARIANT spacing;
        spacing.vt=VT_R8;
        spacing.dblVal=2.0;
    AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlXAxis, L"MajorUnit", 1, spacing);
    }
//Get the active workbook
IDispatch *pXlActiveWorkBook;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveWorkbook", 0);
pXlActiveWorkBook = result.pdispVal;
}

//Save the work book.
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"C:\\output.xls");
VARIANT fformat;
fformat.vt = VT_I4;
fformat.lVal=-4143;
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 1, fname);
}


pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
CoUninitialize();

---

In this second example I was able to get ChartWizard to work. However, I had to put the arguments in in reverse order. Probably something wrong with my Autowrap function. The entire program follows. I still had to set the chart gallery independently. I've found problems before with using gallery type 75 in the chartwizard when using a MFC version of all this.

Code:

---



#include "stdafx.h"
#include <ole2.h> // OLE2 Definitions

// AutoWrap() - Automation helper function...
HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...)
{
// Begin variable-argument list...
va_list marker;
va_start(marker, cArgs);

if (!pDisp)
    {
    MessageBox(NULL, "NULL IDispatch passed to AutoWrap()", "Error", 0x10010);
    _exit(0);
    }

// Variables used...
DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr;
char buf[200];
char szName[200];


// Convert down to ANSI
WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);

// Get DISPID for name passed...
hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
if (FAILED(hr))
    {
    sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
    MessageBox(NULL, buf, "AutoWrap()", 0x10010);
    _exit(0);
    return hr;
    }

// Allocate memory for arguments...
VARIANT *pArgs = new VARIANT[cArgs + 1];
// Extract arguments...
for (int i = 0; i < cArgs; i++)
    {
    pArgs[i] = va_arg(marker, VARIANT);
    }

// Build DISPPARAMS
dp.cArgs = cArgs;
dp.rgvarg = pArgs;

// Handle special-case for property-puts!
if (autoType & DISPATCH_PROPERTYPUT)
    {
    dp.cNamedArgs = 1;
    dp.rgdispidNamedArgs = &dispidNamed;
    }

// Make the call!
hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
if (FAILED(hr))
    {
    sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
    MessageBox(NULL, buf, "AutoWrap()", 0x10010);
    _exit(0);
    return hr;
    }
// End variable-argument section...
va_end(marker);

delete[] pArgs;

return hr;
}
                                       
int main(int argc, char* argv[])
    {
    // Initialize COM for this thread...
    CoInitialize(NULL);
   
    // Get CLSID for our server...
    CLSID clsid;
    HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
    VARIANT vopt;
        vopt.vt=VT_ERROR;
        vopt.scode=DISP_E_PARAMNOTFOUND;
        vopt.lVal=DISP_E_PARAMNOTFOUND;

    if (FAILED(hr))
        {
        ::MessageBox(NULL, "CLSIDFromProgID() failed", "Error", 0x10010);
        return -1;
        }
   
    // Start server and get IDispatch...
    IDispatch *pXlApp;
    hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
    if (FAILED(hr))
        {
        ::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);
        return -2;
        }
   
    // Make it visible (i.e. app.visible = 1)
        {
        VARIANT x;
        x.vt = VT_I4;
        x.lVal = 1;
        AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
        }
       
// Get Workbooks collection
IDispatch *pXlBooks;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
}

IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"c:\\junk.xls\0");
AutoWrap(DISPATCH_METHOD, &result, pXlBooks, L"Open", 1, fname);
pXlBook = result.pdispVal;
}

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

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
pXlSheets = result.pdispVal;
}

IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_I4;
itemn.lVal = 1;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1, itemn);
pXlSheet = result.pdispVal;
}

VARIANT darange;

IDispatch *pXlRange;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"A1:B7");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
darange.vt = VT_DISPATCH;
darange.pdispVal = result.pdispVal;
pXlRange = result.pdispVal;
}

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlRange, L"Select", 0);
}

IDispatch *pXlChartObjects;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"ChartObjects", 0);
pXlChartObjects = result.pdispVal;
}

IDispatch *pXlChartObject;
{
VARIANT result;
VariantInit(&result);
VARIANT left, top, width, height;
left.vt = VT_R8;
left.dblVal = 600.;
top.vt = VT_R8;
top.dblVal = 600.;
width.vt = VT_R8;
width.dblVal = 200.;
height.vt = VT_R8;
height.dblVal = 200.;

AutoWrap(DISPATCH_METHOD, &result, pXlChartObjects, L"ADD", 4, left, top, width, height);
pXlChartObject = result.pdispVal;
}

IDispatch *pXlChart;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlChartObject, L"Chart", 0);
pXlChart = result.pdispVal;
}



//use the chart wizard with the arugments in reverse order
{
VARIANT result;
VARIANT maintitle;
maintitle.vt=VT_BSTR;
maintitle.bstrVal=::SysAllocString(L"Main Title");

VARIANT ytitle;
ytitle.vt=VT_BSTR;
ytitle.bstrVal=::SysAllocString(L"Ytitle");
VARIANT xtitle;
xtitle.vt=VT_BSTR;
xtitle.bstrVal=::SysAllocString(L"Xtitle");
VARIANT gall;
gall.vt=VT_I4;
gall.lVal=-4169;

VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard", 11, vopt,ytitle,xtitle,maintitle,vopt,vopt,vopt,vopt,vopt,gall,darange);
}
//set to xl_XYScatterNoMarkers
{
VARIANT result;
VariantInit(&result);
VARIANT type;
type.vt = VT_I4;
type.lVal = 75;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlChart, L"ChartType", 1, type);
}

//get x axis
IDispatch *pXlXAxis;
        {
    VARIANT result;
        VariantInit(&result);
    VARIANT axisind;
        axisind.vt=VT_I4;
        axisind.lVal=1;
        AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"Axes", 1, axisind);
        pXlXAxis=result.pdispVal;
        }
        {
        VARIANT result;
        VariantInit(&result);
    VARIANT hasgrid;
        hasgrid.vt=VT_BOOL;
        hasgrid.boolVal=TRUE;
    AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlXAxis, L"HasMajorGridlines", 1, hasgrid);

        }
//set x axis grid spacing to 2 units
        {
        VARIANT result;
        VariantInit(&result);
    VARIANT spacing;
        spacing.vt=VT_R8;
        spacing.dblVal=2.0;
    AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlXAxis, L"MajorUnit", 1, spacing);
    }

pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
CoUninitialize();

return 0;
}

---

Thank you very much Tom, it does work it! and I've tested the ChartWizard with the arguments in the reverse order, and it's ok, that was the problem, and this is because I needed to put my range var as the first parameter. And after that I've only had to change the Axes lVal=1 to lVal=2 to specify the major units for the axis y. Thank you.

Hello, i just need to know whats the function, if there's any one, to make disappear in excel all the cells borders, like in word, all in blank.

And i would like to know if there's a way to hide cells, and how to insert an image (gif,jpg,..anyone).

Thanks.

Two partial examples:
In the first part I hide the gridlines. In the second part, I hide a column.

To hide a cell you would set the font color to white. Otherwise you would have to hide whole rows or whole columns.
To do this you would have to select the range, Get the font of the range
and then set the font color to white (if your background is white.
Probably best to protect those cells so noone can edit them

To include an image I generally copy the image to the clipboard, select a cell to position it and then paste the image. You can adjust the positioning of the image after this programmatically.

I include an example of pasting the graphic using MFC. You can translate it to the without MFC version.

Code:

---

//***Don't display grids

 IDispatch *pXlWindows;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Windows", 0);
pXlWindows = result.pdispVal;
}
       
IDispatch *pXlWindow;
{
VARIANT result;
VARIANT xwind;
xwind.vt=VT_I4;
xwind.lVal=1;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlWindows, L"Item", 1,xwind);
pXlWindow = result.pdispVal;
}

{


VARIANT result;
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=FALSE;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlWindow, L"DisplayGridlines", 1,xprop);
}       

//******Hide A Column
IDispatch *pXlRange1;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);

pXlRange1 = result.pdispVal;
}

IDispatch *pXlCols;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Columns", 0);

pXlCols = result.pdispVal;
}

{


VARIANT result;
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=TRUE;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlCols, L"Hidden", 1,xprop);
}

---

Pasting an image using MFC.

Code:

---

COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
OpenClipboard(); // Reserve clipboard for this program
          EmptyClipboard();
          CBitmap MyBitmap;
          MyBitmap.LoadBitmap(IDB_BITMAP1);  // A Bitmap you drew in the
          //  Resource Editor
          HBITMAP MyBitmapHandle = (HBITMAP)MyBitmap;  // Cast it to a HBITMAP
          SetClipboardData(CF_BITMAP, MyBitmapHandle);
          CloseClipboard();
          app.SetVisible(TRUE);
          app.SetUserControl(TRUE);
          sheet.Activate();
          range = sheet.GetRange(COleVariant("A1"), COleVariant("a1"));
          range.Select();
          sheet.Paste(covOptional, covOptional);

---

Thank you, the whiting font is what i did, and that's what i'm gonna do, because the hiding dosen't avoid me to show the graphs, thankyou anyway.

The gridlines has been helpfull to me.

And how could you insert images without MFC, do you have any example?

Thanks Tom.

Importing a picture from a file

Code:

---

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

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Shapes", 0);
pXlShapes = result.pdispVal;
}
IDispatch *pXlShape;
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"C:\\toad1.jpg\0");
VARIANT xpropf;
xpropf.vt=VT_BOOL;
xpropf.boolVal=FALSE;
VARIANT xpropt;
xpropt.vt=VT_BOOL;
xpropt.boolVal=TRUE;

VARIANT xtop;
xtop.vt=VT_R8;
xtop.dblVal=10.0;
VARIANT xleft;
xleft.vt=VT_R8;
xleft.dblVal=10.0;
VARIANT xwidth;
xwidth.vt=VT_R8;
xwidth.dblVal=200.0;
VARIANT xheight;
xheight.vt=VT_R8;
xheight.dblVal=200.0;

//I need to put the arguments in reverse order
AutoWrap(DISPATCH_METHOD, &result, pXlShapes, L"AddPicture", 7,xheight,xwidth,xleft,xtop,xpropt,xpropf,fname);
pXlShape = result.pdispVal;
}

---

Thankyou very much Tom.

Hi Tom, do you know how to get, with c++, the exact PID of the EXCEL opened?

No, I've never tried it.

See this thread and the articles it references:

http://www.codeguru.com/forum/showth...&highlight=PID

thanks, the problem is that i'm having some problems (error which stops the program) with the "CoUninitialize()" function, called when i delete the Excel class, and i don`t know if there's another way of do the delete of the Excel process diferent of CoUninitialize. If you know some way i'll be pleased to read you.

Thanks.

Hi Tom,
Thanks.
I am trying Excel automation. My problem is formatting excel cells;
I cant give font properties (bold,italic,underline, size, type) and
cell format (is cell currency,text,time,date..) and number of digit after decimal.
If you know some way i'll be pleased to read you.

If you are using the non-MFC methods of this thread you
would set the NumberFormat Property of the range class to "0.000" to
get three digits after the decimal.

A MFC version:
Range range;
range=sheet.GetRange(COleVariant("A1","A1"));
range.SetNumberFormat(COleVariant("0.000"));

You would get the dispatch of the Font from the Range
and then set the Name, Bold, Underline, etc properties of the Font class.

Font font;
font=range.GetFont();

font.SetBold(covTrue);
font.SetSize(COleVariant("12"));
font.SetUnderline(COleVariant(2L));
font.SetUnderline(COleVariant(-4142L)) to unset the underline.

Of course with the non-MFC method this would be a little more involved.
I'm feeling lazy this morning or I'd write down a couple of examples.
Maybe I'll do that in a couple of hours when I have time.

Tom

Using the Non-MFC methods I set the number format to three places, change the font to bold and set underline.

Code:

---

IDispatch *pXlRange1;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);

pXlRange1 = result.pdispVal;
}


{ 
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"0.000");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"NumberFormat", 1, frmt);


}



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

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Font", 0);
pXlFont = result.pdispVal;


        }


        {
VARIANT result;
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=TRUE;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Bold", 1,xprop);
        }

        {
VARIANT result;
VARIANT xprop;
xprop.vt=VT_I4;
xprop.lVal=2;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Underline", 1,xprop);
        }

---

Hi Tom,

Thanks but there is an error when calling AutoWrap function. GetIDsOfNames is generate error.

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

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Font", 0);
pXlFont = result.pdispVal;
}

What will be cause for this?
And also how can ı set cell widht and height?

Thanks..

I don't know. The code worked for me. I'd have to see your code leading up to and including the get Font part. I still probably might not be able to tell.

What version of Excel are you using? I was using Excel 2000.

Tom would you be able to duplicate these samples for Lotus-123??

<ducking and running>

ps: Seriously, this is something I think you should put togeter as a quick article here on CG.

Hi Tom,

I solved the problem, thank again.
I need some extra info about excel automation that
how can i set width,height,format( is general,text,numeric), Wrap Text property,font size and type, and alignment properties.

If you know some way i'll be pleased to read you.

Sorry, I've been away on Christmas vacation the last 11 days. You've probably answered these questions long ago but:

1) I don't know how to set height and width of the font independently. The font only has a Size property.

Code:

---

{ 
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=TRUE;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"WrapText", 1, xprop);

}
//left=-4131 center=-4128 right= -4152 general=1
{ 
VARIANT xprop;
xprop.vt=VT_I4;
xprop.lVal=-4131L;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"HorizontalAlignment", 1, xprop);
}

IDispatch *pXlFont;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Font", 0);
pXlFont = result.pdispVal;
}

{ 
VARIANT nme;
nme.vt = VT_BSTR;
nme.bstrVal = ::SysAllocString(L"Brush Script");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Name", 1,nme);

}

{ 
VARIANT hgt;
hgt.vt = VT_I4;
hgt.lVal=20;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Size",1,hgt);
//I don't know how to set height and width independently
}

---

Hi Tom;

I want to freeze the first row when i export data to excel. It can be done in VB by "FreezePanes" property set to TRUE.
I cant use it in my Excel Automation. It gives error.
How can i use "FreezePanes" in our Excel Automation.

Thanks.

Using the methods of this thread starting with the XlApp dispatch, To freeze the first row and column select the B2 cell.

To freeze the first row select the entire second row.
To freeze the first column select the entire B column.

Code:

---

IDispatch *pXlWindows;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Windows", 0);
pXlWindows = result.pdispVal;
}
       
IDispatch *pXlWindow;
{
VARIANT result;
VARIANT xwind;
xwind.vt=VT_I4;
xwind.lVal=1;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlWindows, L"Item", 1,xwind);
pXlWindow = result.pdispVal;
}

IDispatch *pXlRange2;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"B2");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1,range);

pXlRange2 = result.pdispVal;
}

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlRange2, L"Select", 0);
}       



{
VARIANT result;
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=TRUE;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlWindow, L"FreezePanes", 1,xprop);
}

---

To use MFC you would use some variant on

Code:

---

Windows wnds;
wnds=app.GetWindows();
Window wnd;
wnd=wnds.GetItem(COleVariant(1L));
Range rng;
rng=sheet.GetRange(COleVariant("B2"),COleVariant("B2"));
rng.Select();
wnd.SetFreezePanes(COleVariant((short)TRUE));

---

The method names might be a little different depending on what version of Visual C++ you are using.

Hi, does anybady know if there's any function or way to free all the memory used in c++ till that moment? i'm having problems with a program which ends after using during a period of time (not short but not long) and i've revised too many times the code and i think that i'm freeing well, but the program ends at different places without a reasonable explanation, and it smells me that it could be a memory problem, out of memory. Thanks

If I were you, I would delete your previous post and then repost it in the Visual C++ forum as a new thread. It is a different topic and the title of this thread will kind of stop people from reading it.

Tom

Hi Tom;

How can i give Font and BackGround color?

Thanks for your help.

Below I set the font color to red and the background of the cell to green.

Code:

---

IDispatch *pXlRange1;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);

pXlRange1 = result.pdispVal;
}


IDispatch *pXlInside;
{ 

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Interior", 0);

pXlInside = result.pdispVal;
}
{ 
VARIANT color;
color.vt = VT_I4;
color.lVal=RGB(0,255,0);
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlInside, L"Color",1,color);

}



IDispatch *pXlFont;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Font", 0);
pXlFont = result.pdispVal;
}


{ 
VARIANT color;
color.vt = VT_I4;
color.lVal=RGB(255,0,0);
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Color",1,color);

}

---

Tom have you looked if after using Autowrap functions and doing things creating a xls file, do you have memory RAM problems, because is exactly what it happens to me. The Ram use increases without reason, and i don't know why. And i think that is not the best when a .exe is running, and that's becaues it crash after a running time, there's no memory!

The truth? Okay, I haven't experienced the memory problems because I use the MFC based method of Automation based on importing a type library.

I didn't even start this example. It didn't come from this thread. I got the core code off of somebody else who was having trouble making it work.

I didn't do anything like clearing the Variants with VariantClear() and I didn't release all of the Dispatchs. Couldn't actually say these are necessary. Could be they are necessary.
VariantClear
SysFreeString

My case is not usual, because i want to test how long last the program could be, but my question is if you, watching the task manager, see the same that i see, that is that since the "Excel.exe" appears in the list (task manager) the ram memory begins to increase, and when the "Excel.exe" disappears(it has been deleted the excel class) the ram memory doesn't come back to the previous state. The only reason i could imagine is that Autowraps function consumes too much ram memory, and i would want to know if anyone could see something similar in the task manager, and if there's any way to stop that increase of ram use, because i think that the normal should be that after creating the excel, the aplication's ram consumption would come back to the previous stat once that has ended the job.

Because if you only want to run it once there's no problem, but if you want to do it more times the ram memory is a real problem.

Have you tried releasing all the dispatches and clearing all the VARIANTs after you use them?

Hi Tom;

I want to format cell in the excel form.
For example I send a date value as a long to excel. in the excel that cell will not be seen as a date format. Like this the i cant give numeric format to the cell.
Haw can i give format of the cell for excel.

Thanks.

Hi,

Use OLE Automation.

Here is a link may be useful to you !

http://www.codeguru.com/cpp/data/mfc...le.php/c11745/


-Anant

Quote:

---

Originally Posted by neyadakim

Hi Tom;

I want to format cell in the excel form.
For example I send a date value as a long to excel. in the excel that cell will not be seen as a date format. Like this the i cant give numeric format to the cell.
Haw can i give format of the cell for excel.

Thanks.

---

Are you saying that the method above for setting the number format didn't work? I'm repeating that section below showing how to use it to format a date.

Code:

---

IDispatch *pXlRange1;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);

pXlRange1 = result.pdispVal;
}


{ 
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"0.000");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"NumberFormat", 1, frmt);


}

//To set a date format
{ 
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"dd-mmm-yy");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"NumberFormat", 1, frmt);


}

---

From Excel help the date formats are
To display Use this code
Months as 1–12 M
Months as 01–12 Mm
Months as Jan–Dec Mmm
Months as January–December Mmmm
Months as the first letter of the month Mmmmm
Days as 1–31 D
Days as 01–31 Dd
Days as Sun–Sat Ddd
Days as Sunday–Saturday Dddd
Years as 00–99 Yy
Years as 1900–9999 Yyyy


Hours, minutes, and seconds To display hours, minutes, and seconds, include the following format codes in a section.

To display Use this code
Hours as 0–23 H
Hours as 00–23 Hh
Minutes as 0–59 M
Minutes as 00–59 Mm
Seconds as 0–59 S
Seconds as 00–59 Ss
Hours as 4 AM h AM/PM
Time as 4:36 PM h:mm AM/PM
Time as 4:36:03 P h:mm:ss A/P
Elapsed time in hours; for example, 25.02 [h]:mm
Elapsed time in minutes; for example, 63:46 [mm]:ss
Elapsed time in seconds [ss]
Fractions of a second h:mm:ss.00


AM and PM If the format contains an AM or PM, the hour is based on the 12-hour clock, where "AM" or "A" indicates times from midnight until noon and "PM" or "P" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock. The "m" or "mm" code must appear immediately after the "h" or "hh" code or immediately before the "ss" code; otherwise, Microsoft Excel displays the month instead of minutes.

Hi tom;
Thanks for help.
Tom i wrote a Excel Automation control and added to my project.It seems that i must have a more knowledge about excel automation to cover all option of excel. And i want to learn all aspact of the excel automation.
Can you give me an like referans to learn all aspact of the excel automation.

Thanks.