Open Excel in VC++ and write data

Hi TOM,

realy so much thanx for ur code!

i m sorry to late thanx.

coz i was out station.

now i hav another question to u is that

i want to set image to the xlRange given by us.

for example COleVariant(L"I4:M19");

so plz help me for this.............

once more thanx for ur code realy i helped it a lot.

i waiting for ur relpy.

[email protected]

Quote:

---

Originally Posted by DnyanAnand

Hi TOM,

realy so much thanx for ur code!

i m sorry to late thanx.

coz i was out station.

now i hav another question to u is that

i want to set image to the xlRange given by us.

for example COleVariant(L"I4:M19");

so plz help me for this.............

once more thanx for ur code realy i helped it a lot.

i waiting for ur relpy.

[email protected]

---

"Love starts with a smile, grows with a kiss and ends with a tear"

Previously posted was importing an image from a file. In it we positioned the image using the coordinates of the top,left, width and height of a picture. I repeat that cod at the end down below.

Given a range like "I4:M19" I would get the range

Code:

---

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

pXlRange1 = result.pdispVal;

}

---

now I would try and get the top,left,width and height properties from the range object.

Code:

---

double thetop;
{
VARIANT result;
unsigned short vals;
vals=(unsigned short)0x0000007e;
AutoWrap(DISPATCH_PROPERTYGET,&result,pXlRange1,L"Top",0);
thetop=result.dblVal;
}

---

Repeat the above for Left, Width and Height.
use these number in add picture below.

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;
}

---

Hi Tom,

I m so much thanx to u...

this code of seting image in range is helped me a lot.......

if i will get some doubt realy i will call u rather than anyone else......

hey i want to know that can u solve the problem of csharp also?

if then i will definately ask u......

i m realy new to this field...........

just having six month now wrking in one small scale company..........

Hi Tom,

i hav one more problem with this code ,

i have one file image in clipboard, so i hav to create a bitmap & then hav to

use in this code. it is tediuos task.

now i want to use the clipboard image directly to be paste in this range.

plz help me for this........


thanx in advance

Best Regards

DnyanAnand
(Budding software Engg.)

I won't worry about how you get the image into the clipboard. I started by copying a bitmap image into the clipboard.

From the previous post you can get the range and then get coordinates of the paste area.

You will A) paste the image to the excel sheet.
B) get the shapes class
C) get the particular shape to resize
D) reset the height and width to the desired values.

Code:

---

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

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

{
VARIANT result;
AutoWrap(DISPATCH_METHOD,&result,pXlSheet,L"Paste",0);
}

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

//get the shape
//I assume the image is shape 1.

IDispatch *pXlShape;
{
VARIANT result;
VariantInit(&result);
VARIANT xitem;
xitem.vt=VT_I4;
xitem.lVal=1;
AutoWrap(DISPATCH_METHOD, &result, pXlShapes, L"Item", 1,xitem);
pXlShape = result.pdispVal;
}

//set the width
{
VARIANT result;
VariantInit(&result);
VARIANT xwidth;
xwidth.vt=VT_R8;
xwidth.dblVal=400.0;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlShape, L"Width", 1,xwidth);

}

---

sorry i would have send u the thing how to get the image from clipboard.

now plz see i have the code is like this

OpenClipboard();
HBITMAP hBmp = (HBITMAP)GetClipboardData(CF_BITMAP);

now i have to assing this bitmap to variant

like
Variant var;
var.vt = ? //which property shud i use...

if i use VT_CF it is for clipborad format directly.

then how should be assinged........plz specify it.

if i use the VT_BLOB it is for byte then how to use it

or shud i use any thing else to assing bitmap handle to Variant....


i will try some how with this ur code if possible then i will tell u........

but i know i cant think so briliant so plz help me for this.....

Thanx in advance

Hi Tom;

I want to format cell in the excel form.
I send a time value as a long to excel and want to see that value as time value.
Can you give an example for time format of the cell for excel.
I tried but Excel show me null value for each test.

Thanks.

Code:

---

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

pXlRange2 = result.pdispVal;

}

{ 
VARIANT value;
value.vt = VT_I4;
value.lVal = 39848;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange2, L"Value", 1,value);
}

{ 
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"mmmm d, yyyy");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange2, L"NumberFormat", 1, frmt);

VariantClear(&frmt);

}

---

Hi, I tried opening and writing data in excel as explained in this thread. Everything worked great until it got to the save as part. the file I opened was a txt file(tab delimited file) and I colored some cells. but after I saved the file as "output.xls", I reopened the file and all the colors were lost. It seems the format isn't changed at all.
My code:

VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal = ::SysAllocString(L"D:\\data\\output.xls");
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 1, fname );

I know there is a format that I should use. but when i put this


VARIANT fformat;
fformat.vt = VT_I4;
fformat.lVal=-4143;
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 2, fformat, fname );

the code breaks at the last line and it is not working. Please help!

Hi TOM,

Plz I need your Help in,
1] Delete single Row.
2] Delete Single Column
3] Add Single Row
4] Add single Column
5] Add new Sheet in workBook

Thanx in Advance........I will be waiting for ur reply

[email protected]

First get the range of the cell where you want to insert or delete.

Code:

---

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;
}

---

If you want to insert or delete a column get the EntireRow Or the EntireColumn.
To delete a row

Code:

---

IDispatch *pXlRow;

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange2, L"EntireRow",0);
pXlRow = result.pdispVal;
AutoWrap(DISPATCH_METHOD, &result, pXlRow, L"Delete", 0);
}

---

To insert a row

Code:

---

IDispatch *pXlRow;

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange2, L"EntireRow",0);
pXlRow = result.pdispVal;
AutoWrap(DISPATCH_METHOD, &result, pXlRow, L"Insert", 0);
}

---

Replace the word row above with column to and you have the methods for columns.

To delete a sheet.

Code:

---

IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_BSTR;
itemn.bstrVal = ::SysAllocString(L"Sheet3");;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1, itemn);
pXlSheet = result.pdispVal;
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"Delete", 0);

}

---

To insert a sheet before Sheet3

Code:

---

IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_BSTR;
itemn.bstrVal = ::SysAllocString(L"Sheet3");
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1, itemn);
pXlSheet = result.pdispVal;
}
IDispatch *pXlSheet2;
{
VARIANT result;
VariantInit(&result);
VARIANT before;
before.vt = VT_DISPATCH;
before.pdispVal = pXlSheet;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Add", 1, before);
pXlSheet2 = result.pdispVal;
}

---

Thank you very much! Tom

I need the code for after "sheet3" in the same way

VARIANT After;
After.vt = VT_DISPATCH;
After.pdispVal = pXlSheet;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Add", 1, After);

i will be witing for your reply!

Thanks In Advance.

Using an optional argument I have versions for before and after here.

Before

Code:

---

IDispatch *pXlSheet2;
{
VARIANT result;
VariantInit(&result);
VARIANT Optional;
Optional.vt=VT_ERROR;
//DISP_E_PARAMNOTFOUND defined in WINERROR.H
Optional.scode=(long)DISP_E_PARAMNOTFOUND;
VARIANT before;
before.vt = VT_DISPATCH;
before.pdispVal = pXlSheet;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Add", 1, before);
pXlSheet2 = result.pdispVal;
}

---

After

Code:

---

IDispatch *pXlSheet2;
{
VARIANT result;
VariantInit(&result);
VARIANT Optional;
Optional.vt=VT_ERROR;
//DISP_E_PARAMNOTFOUND defined in WINERROR.H
Optional.scode=(long)DISP_E_PARAMNOTFOUND;
VARIANT after;
after.vt = VT_DISPATCH;
after.pdispVal = pXlSheet;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Add", 2, after,Optional);
pXlSheet2 = result.pdispVal;
}

---

*************Thank you very much! Tom*************


Now i am coming with some new problem towords you.

That is i want to print the Whole active sheet. so plz help me for this stuff.


Thanks In Advance..........

Dnyan
You can't shake hands with a clenched fist............................