Board index » delphi » Help with Excel! Using typelib...

Help with Excel! Using typelib...

I have seen this question asked here before, but there was never an answer
given...

When programming without the TYPELIB using variants, you can easily add new
worksheets and workbooks using the Add method like this...

...
var
  Workbook, WorkSheet, XLApp : Variant;
begin
    XLApp := CreateOLEObject( 'Excel.Application' );
    WorkBook := XLApp.WorkBooks.Add;
    WorkBook.WorkSheets.Add;
    WorkBook.WorkSheets.Add;

    WorkBook.WorkSheets['Sheet1'].Activate;
    WorkSheet := WorkBook.ActiveSheet;
    WorkSheet.Name := 'Screen Info #1';
...

This code creates a new workbook that contains the default 3 worksheets and
adds two more sheets to it, then renames the first sheet.

I am trying to do this using the typelibrary, however I cannot figure out
how to get a Sheets object returned so that I can change the name of a
sheet or do whatever.  This is what I have so far...

...
var
  LCID : integer;
  XLApp : XLApplication;
  XLWorkBook : WorkBook;
  XLWorkSheet : WorkSheet;
begin
    LCID := GetUserDefaultLCID;
    XLApp := CoApplication.Create;
    XLApp.SheetsInNewWorkBook[LCID] := 5;
    XLWorkBook := XLApp.Workbooks.Add(Null, LCID);
    XLWorkSheet := XLApp.Sheets['Sheet1'] as WorkSheet;
    XLWorkSheet.Name := 'Screen Info #1';
...

This code should open a new workbook with 5 sheets in it, then change the
name of the first sheet.  I get an exception on the 'as WorkSheet' line.  I
saw where someone in the newsgroup suggested this, but I cannot get it to
work.  In the Delphi help, under IDispatch, you can see where they say it
will raise an exception if you try to do any slick typecasting.
So my questions are, how can you rename a sheet using the typelib?  How can
you get a sheets object returned when everything returns IDispatch?  Please
help!  I have been stuck on this toooooo long...

I am using Delphi 3 by the way...

Any help is GREATLY appreciated!

-Chad

 

Re:Help with Excel! Using typelib...


Quote
Chad Harrelson wrote:
> I have seen this question asked here before, but there was never an answer
> given...

> When programming without the TYPELIB using variants, you can easily add new
> worksheets and workbooks using the Add method like this...

> ...
> var
>   Workbook, WorkSheet, XLApp : Variant;
> begin
>     XLApp := CreateOLEObject( 'Excel.Application' );
>     WorkBook := XLApp.WorkBooks.Add;
>     WorkBook.WorkSheets.Add;
>     WorkBook.WorkSheets.Add;

>     WorkBook.WorkSheets['Sheet1'].Activate;
>     WorkSheet := WorkBook.ActiveSheet;
>     WorkSheet.Name := 'Screen Info #1';
> ...

> This code creates a new workbook that contains the default 3 worksheets and
> adds two more sheets to it, then renames the first sheet.

> I am trying to do this using the typelibrary, however I cannot figure out
> how to get a Sheets object returned so that I can change the name of a
> sheet or do whatever.  This is what I have so far...

> ...
> var
>   LCID : integer;
>   XLApp : XLApplication;
>   XLWorkBook : WorkBook;
>   XLWorkSheet : WorkSheet;
> begin
>     LCID := GetUserDefaultLCID;
>     XLApp := CoApplication.Create;
>     XLApp.SheetsInNewWorkBook[LCID] := 5;
>     XLWorkBook := XLApp.Workbooks.Add(Null, LCID);
>     XLWorkSheet := XLApp.Sheets['Sheet1'] as WorkSheet;
>     XLWorkSheet.Name := 'Screen Info #1';
> ...

> This code should open a new workbook with 5 sheets in it, then change the
> name of the first sheet.  I get an exception on the 'as WorkSheet' line.  I
> saw where someone in the newsgroup suggested this, but I cannot get it to
> work.  In the Delphi help, under IDispatch, you can see where they say it
> will raise an exception if you try to do any slick typecasting.
> So my questions are, how can you rename a sheet using the typelib?  How can
> you get a sheets object returned when everything returns IDispatch?  Please
> help!  I have been stuck on this toooooo long...

> I am using Delphi 3 by the way...

> Any help is GREATLY appreciated!

> -Chad

 Make sure that the Sheets[] method/property returns a WorkSheet object and not
a Sheets object.  The 'as' operator essentilly does a QueryInterface on the LHS
object to see if it supports the RHS object.  You should be able to see in the
_TLB.PAS file what Sheets[] returns...

-Adam
Delphi Developer Support

(I do not respond to unsolicited e-mail.  Thank you.)

Re:Help with Excel! Using typelib...


Hello,

That should work ok. What version of Excel are you using?

have fun,
--
Binh Ly
Brickhouse Data Systems, Inc.
http://www.brickhouse.com

Quote
Chad Harrelson wrote in message <01bdcb49$7b942740$afb9d6d1@elite>...
>I have seen this question asked here before, but there was never an answer
>given...

>When programming without the TYPELIB using variants, you can easily add new
>worksheets and workbooks using the Add method like this...

>...
>var
>  Workbook, WorkSheet, XLApp : Variant;
>begin
>    XLApp := CreateOLEObject( 'Excel.Application' );
>    WorkBook := XLApp.WorkBooks.Add;
>    WorkBook.WorkSheets.Add;
>    WorkBook.WorkSheets.Add;

>    WorkBook.WorkSheets['Sheet1'].Activate;
>    WorkSheet := WorkBook.ActiveSheet;
>    WorkSheet.Name := 'Screen Info #1';
>...

>This code creates a new workbook that contains the default 3 worksheets and
>adds two more sheets to it, then renames the first sheet.

>I am trying to do this using the typelibrary, however I cannot figure out
>how to get a Sheets object returned so that I can change the name of a
>sheet or do whatever.  This is what I have so far...

>...
>var
>  LCID : integer;
>  XLApp : XLApplication;
>  XLWorkBook : WorkBook;
>  XLWorkSheet : WorkSheet;
>begin
>    LCID := GetUserDefaultLCID;
>    XLApp := CoApplication.Create;
>    XLApp.SheetsInNewWorkBook[LCID] := 5;
>    XLWorkBook := XLApp.Workbooks.Add(Null, LCID);
>    XLWorkSheet := XLApp.Sheets['Sheet1'] as WorkSheet;
>    XLWorkSheet.Name := 'Screen Info #1';
>...

>This code should open a new workbook with 5 sheets in it, then change the
>name of the first sheet.  I get an exception on the 'as WorkSheet' line.  I
>saw where someone in the newsgroup suggested this, but I cannot get it to
>work.  In the Delphi help, under IDispatch, you can see where they say it
>will raise an exception if you try to do any slick typecasting.
>So my questions are, how can you rename a sheet using the typelib?  How can
>you get a sheets object returned when everything returns IDispatch?  Please
>help!  I have been stuck on this toooooo long...

>I am using Delphi 3 by the way...

>Any help is GREATLY appreciated!

>-Chad

Other Threads