Board index » delphi » D5 and reading/writing to Excel97 cells

Anders Harvyl

Delphi Developer
Thu, 07 Mar 2002 03:00:00 GMT

D5 and reading/writing to Excel97 cells

Thank you Borland/Inprise for supplying OleServers in Delphi 5, and
examples of Word97 and Powerpoint 97 applications. Howevere there are no
examples of Excel97 applications. WHY? It's (for me anyway) extremely
difficult to find out how to make the very simplest example of just
creating a worksheet and setting a cells value to 'foo' or just
anything.
My approach was something like:
...
      ExcelApplication1.Workbooks.Add(xlWBATWorksheet, lcid);
      ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Item[1]);
      ExcelWorksheet1.ConnectTo(_worksheet(ExcelWorkbook1.Sheets[1]));
      ExcelWorksheet1.Cells.Item[10, 10] := 'foo';
...
Running the application correctly opens Excel97 (if it is not already
open), adds an empty Workbook, and an empty Worksheet. However the last
call to "ExcelWorksheet1.Cells.Item[10, 10] := 'foo';" creates an
exception "...No such interface supported...".
HOW DO I SET the cellvalue with row number 10 and column 10 to "foo" (or
whatever)?
Is it really too much to ask for such an example from Borland/Inprise?
OK, maybe there are some books on the issues, I could as a last resort
accept that. Please name useful examples of sources where to find such
examples!
Regards
Anders L. Harvyl
 
Chris Rober

Delphi Developer
Thu, 07 Mar 2002 03:00:00 GMT

Re:D5 and reading/writing to Excel97 cells


Quote
Anders Harvyl <ahar...@ahdp.se> wrote:
>      ExcelApplication1.Workbooks.Add(xlWBATWorksheet, lcid);
>      ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Item[1]);
>      ExcelWorksheet1.ConnectTo(_worksheet(ExcelWorkbook1.Sheets[1]));
>      ExcelWorksheet1.Cells.Item[10, 10] := 'foo';
>...
>Running the application correctly opens Excel97 (if it is not already
>open), adds an empty Workbook, and an empty Worksheet. However the last
>call to "ExcelWorksheet1.Cells.Item[10, 10] := 'foo';" creates an
>exception "...No such interface supported...".
>HOW DO I SET the cellvalue with row number 10 and column 10 to "foo" (or
>whatever)?

How about:
  ExcelWorksheet1.Cells.Item[10, 10].Value := 'foo';

--
Regards,
Chris Roberts

Orlin Tomo

Delphi Developer
Fri, 08 Mar 2002 03:00:00 GMT

Re:D5 and reading/writing to Excel97 cells


hello Anders,

try with:
  ExcelWorksheet1.Range ['J10', 'J10'].Value := 'foo';

Orlin.

Anders Harvy

Delphi Developer
Fri, 08 Mar 2002 03:00:00 GMT

Re:D5 and reading/writing to Excel97 cells


Chris,
Have you really tried this yourself? It does not work for me. Same exception
as I originally wrote about: "...No such interface supported...".
/Anders
Quote
Chris Roberts wrote:
> Anders Harvyl <ahar...@ahdp.se> wrote:

> >      ExcelApplication1.Workbooks.Add(xlWBATWorksheet, lcid);
> >      ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Item[1]);
> >      ExcelWorksheet1.ConnectTo(_worksheet(ExcelWorkbook1.Sheets[1]));
> >      ExcelWorksheet1.Cells.Item[10, 10] := 'foo';

> >...
> >Running the application correctly opens Excel97 (if it is not already
> >open), adds an empty Workbook, and an empty Worksheet. However the last
> >call to "ExcelWorksheet1.Cells.Item[10, 10] := 'foo';" creates an
> >exception "...No such interface supported...".
> >HOW DO I SET the cellvalue with row number 10 and column 10 to "foo" (or
> >whatever)?

> How about:
>   ExcelWorksheet1.Cells.Item[10, 10].Value := 'foo';

> --
> Regards,
> Chris Roberts

Anders Harvy

Delphi Developer
Fri, 08 Mar 2002 03:00:00 GMT

Re:D5 and reading/writing to Excel97 cells


Orlin,
Have you really tried this yourself? It does not work for me. I get an
exception "...exception class EOleException with message OLE error
C0000005..."
/Anders
Quote
Orlin Tomov wrote:
> hello Anders,

> try with:
>   ExcelWorksheet1.Range ['J10', 'J10'].Value := 'foo';

> Orlin.

Deborah Pat

Delphi Developer
Fri, 08 Mar 2002 03:00:00 GMT

Re:D5 and reading/writing to Excel97 cells


<<Anders Harvyl:
HOW DO I SET the cellvalue with row number 10 and column 10 to "foo"
(or whatever)?

Quote

The way you thought. The problem isn't with
the assignment, it's with that _worksheet
typecast on the line before. Sheets[1] returns
an IDispatch, so you need to cast it to a
_WorkSheet properly, like this:

ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Sheets[1] as _Worksheet);
ExcelWorksheet1.Cells.Item[10, 10] := 'foo';

Charlie Calvert (of Borland) and Graham Marshall
have written some help on automating Excel:
http://www.borland.com/delphi/papers/microexcel/
http://vzone.{*word*269}.net/graham.marshall/excel.htm

HTH
Deborah Pate

Anders Harvy

Delphi Developer
Sat, 09 Mar 2002 03:00:00 GMT

Re:D5 and reading/writing to Excel97 cells


Deborah,
Thanks a lot! I did not realize that the error occured earlier in the
code. I must admit I do not completely understand the mechanism. I am
definitely of the opinion that Borland has a great responsibility to
tell us, the users, to get even the basics out of the product. After all
I have a so called "Professional" version!
What has Borland to offer on their site? An example of Excel automation
with Delphi 3 (THREE!). Sigh. Why do they not have examples for the
latest versions? After all we buy new versions to get the most out of
new functions! How I am supposed to learn about them if they are not
properly documented?
As I said in an earlier message I could accept to buy a book that give
clear code examples (but I still mean it's Borland's responsibility to
supply them in a professional package though). But, anyone, WHERE do I
find that kind of a book?
Well, anyway thanks again Deborah for helping me!
Regards,
Anders
Quote
Deborah Pate wrote:
> <<Anders Harvyl:
> HOW DO I SET the cellvalue with row number 10 and column 10 to "foo"
> (or whatever)?

> The way you thought. The problem isn't with
> the assignment, it's with that _worksheet
> typecast on the line before. Sheets[1] returns
> an IDispatch, so you need to cast it to a
> _WorkSheet properly, like this:

> ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Sheets[1] as _Worksheet);
> ExcelWorksheet1.Cells.Item[10, 10] := 'foo';

> Charlie Calvert (of Borland) and Graham Marshall
> have written some help on automating Excel:
> http://www.borland.com/delphi/papers/microexcel/
> http://vzone.{*word*269}.net/graham.marshall/excel.htm

> HTH
> Deborah Pate

Other Threads

1. Speed when reading/writing text from cells in a Word Table

2. Speed when reading/writing text in cells in a table (MSWord)

3. Write and read on TChart also with D5

4. D5 and excel97, 2000, Multiple Interfaces

5. disabling ecel messages...D5 / Excel97

6. Read from Excel97 .xls file

7. Reading and Writing Record Type (structure) in BLOB field - Stream read error

8. Read HTML source - Read/Write Excel

9. how to merge a cell in excell in d5 server component

10. Copy Bitmap to Cell D5 / EXCEL OLE