Excel Automation - Please help ...

[GuyM]

This is the link to the example: http://support.microsoft.com/kb/308407[^] As you can see there, the example doesn't open an existing excel file but creates a new (unnamed) one. Second, is uses COleSafeArray in order to access (read/write) an entire range of cells, where I need to access only single cells ... Thanks for your help ... :cool:

[David Crow]

GuyM wrote:

My question is how do I open an existing XLS file...

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

Workbooks books = app.GetWorkbooks();

\_Workbook book = books.Open("C:\\\\Book1.xls",
                             vtOptional, vtOptional, vtOptional,
                             vtOptional, vtOptional, vtOptional,
                             vtOptional, vtOptional, vtOptional,
                             vtOptional, vtOptional, vtOptional);
...

}

---

"A good athlete is the result of a good and worthy opponent." - David Crow

"To have a respect for ourselves guides our morals; to have deference for others governs our manners." - Laurence Sterne

[GuyM]

Great - it worked !! Thanks !! Now just one more thing ...how do I get/set the value a certain cell (for example A1).

[Roger Stoltz]

Before you continue you have to be aware that Excel deals with containers. This means that when you launch Excel with CreateDispatch or similar, the next step is usually to get an interface for the WorkBook collection/container called WorkBooks. The same goes for the WorkSheet objects in the WorkSheets collection/container and so on. Once you've got hold of the WorkBooks interface, you call WorkBooks::Open(...) in order to open an existing document, a WorkBook. Then you'll get a WorkBook interface to be able to access the document. When the WorkBook has been opened, you have to choose which WorkSheet you want to use with WorkBook::get_WorkSheets() and later WorkSheets::get_Item(...). For each cell you want to read or write, you have to select a "Range". The example shows this. Regarding the fact that you don't want to use a SafeArray to write the data, I think you can give a VARIANT as argument to Range::put_Value() with a single value and the type set to e.g. VT_I4 for a 32-bit integer.

---

"It's supposed to be hard, otherwise anybody could do it!" - selfquote
"High speed never compensates for wrong direction!" - unknown

[David Crow]

GuyM wrote:

...how do I set the value a certain cell (for example A1).

Worksheets sheets = book.GetSheets();

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

Range range = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));

range.SetValue(COleVariant("12345.6"));

---

"A good athlete is the result of a good and worthy opponent." - David Crow

"To have a respect for ourselves guides our morals; to have deference for others governs our manners." - Laurence Sterne

[hellogany]

HI i want to set a particular cell to RED color ?? and also while quittin the application, the excel file need to be saved....

[David Crow]

hellogany wrote:

HI i want to set a particular cell to RED color ??

Range range = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
Interior interior = range.GetInterior();
interior.SetColorIndex(COleVariant(3L));

hellogany wrote:

and also while quittin the application, the excel file need to be saved....

So you might want to call Save() or SaveAs().

"Love people and use things, not love things and use people." - Unknown

"The brick walls are there for a reason...to stop the people who don't want it badly enough." - Randy Pausch

[hellogany]

thanks for the reply. i have to find a value in the excel cell and should replace a new value in that cell Can u help me on this issue? gany

[David Crow]

hellogany wrote:

i have to find a value in the excel cell...

Look at the Range::Find() method.

"Love people and use things, not love things and use people." - Unknown

"The brick walls are there for a reason...to stop the people who don't want it badly enough." - Randy Pausch

[thready]

DavidCrow wrote:

Range range = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));Interior interior = range.GetInterior();interior.SetColorIndex(COleVariant(3L));

I've been looking all over for how to change the color - thanks to you it works - but just a simple question- Why is 3L = red? How does this color index thing work? Is there a way to use RGB with the 3 params to get any color? Thank you again! Mike

[David Crow]

thready wrote:

Why is 3L = red?

Because that's what the designer of that type library decided.

thready wrote:

How does this color index thing work?

I'm not sure what you are asking here. Just use the OLE/COM Object Viewer to find out the other values.

"Love people and use things, not love things and use people." - Unknown

"The brick walls are there for a reason...to stop the people who don't want it badly enough." - Randy Pausch