Board index » delphi » WorkSheet Collection - loop thru to get names

WorkSheet Collection - loop thru to get names

I've recently written a Delph dll to be imported by C++ builder.
The approach is based on Deborah Pate's illustration on her website(thank
you again, Deborah).

I can load a specific Workbook and then kill Excel from C++ Builder but I'm
having a problem figuring out the syntax to catch the names of the
spreadsheet tab names (i.e. "Sheet1", "Sheet2")

What I'd like to to do ultimately is to get the list of sheet names and
select one given the arugument of a functions.

The relevant code is as follows:

var
  XLA         : TExcelApplication;
  XLWb        : TExcelWorkbook;
  XLWs        : TExcelWorksheet;

// ******************************************************)
procedure LoadXL(WBName : PChar); stdcall;
begin
  Application.ProcessMessages;

  Coinitialize(nil);

  XLWb := TExcelWorkbook.Create(nil);
  XLWs := TExcelWorksheet.Create(nil);
  XLA  := TExcelApplication.Create(nil);

  XLA.ConnectKind := ckNewInstance;
  Application.ProcessMessages;

  XLlcid := GetUserDefaultLCID;
  Application.ProcessMessages;

  XLA.Visible[XLlcid]:= false;

   FileNameOLE := StrPas(WBName);
   TitleName := StrPas(WBName);

   XLA.Workbooks.Open(FileNameOLE,
                      EmptyParam, EmptyParam, EmptyParam, EmptyParam,
                      EmptyParam, EmptyParam, EmptyParam, EmptyParam,
                      EmptyParam, EmptyParam, EmptyParam, EmptyParam,
                      XLlcid);

  XLWb.ConnectTo(XLA.ActiveWorkBook);
  XLWs.ConnectTo(XLWb.Worksheets[1] as _WorkSheet);

  XLA.ScreenUpdating[XLlcid] := true;
  XLA.ActiveWindow.WindowState := xlMaximized;

  XLA.DisplayAlerts[XLlcid] := false;

  XLA.Visible[XLlcid]:= true;
  XLA.UserControl := true;

  Application.ProcessMessages;
end;

I've tried every logical combination of XLA, XLWb, XLWs with Name, Names,
Activate, Select etc that I can think of but am still at a tolal loss as to
what the syntax is for:

i) getting the names of the spreadsheets of the Workbook  (that I can
already successfully access), and

ii) activating s specific spreadsheet based on a parameter supplied in a
function (e.g  procedure SelectSpreadSheet(WSName : PChar); stdcall;)

Any help would be greatly appreciated.

Thank you,
Marc Pitoniak

 

Re:WorkSheet Collection - loop thru to get names


<<Marc F. Pitoniak:
What I'd like to to do ultimately is to get the list of
sheet names and select one given the arugument of a
functions.

Quote

A little example:

var
  i: integer;
begin
  WS.ConnectTo(Excel.ActiveSheet as _Worksheet);
  WS.Name := 'New name';

  WBk.ConnectTo(Excel.ActiveWorkbook);
  for i := 1 to WBk.Worksheets.Count do
  begin
    Memo1.Lines.Append(
      (WBk.Worksheets[i] as _Worksheet).Name);
  end;

  WS.ConnectTo(WBk.Worksheets['Sheet2'] as _Worksheet);
  WS.Activate;

--
Deborah Pate (TeamB) http://delphi-jedi.org

  Use Borland servers; TeamB don't see posts via ISPs
  http://www.borland.com/newsgroups/genl_faqs.html

Re:WorkSheet Collection - loop thru to get names


Thanks Deborah.

Your suggestion worked like a charm.

-Marc

"Deborah Pate (TeamB)" <d.p...@cableinet.co.not-this-bit.uk> wrote in
message news:VA.000008eb.000f5818@cableinet.co.not-this-bit.uk...

Quote
> <<Marc F. Pitoniak:
> What I'd like to to do ultimately is to get the list of
> sheet names and select one given the arugument of a
> functions.

> A little example:

> var
>   i: integer;
> begin
>   WS.ConnectTo(Excel.ActiveSheet as _Worksheet);
>   WS.Name := 'New name';

>   WBk.ConnectTo(Excel.ActiveWorkbook);
>   for i := 1 to WBk.Worksheets.Count do
>   begin
>     Memo1.Lines.Append(
>       (WBk.Worksheets[i] as _Worksheet).Name);
>   end;

>   WS.ConnectTo(WBk.Worksheets['Sheet2'] as _Worksheet);
>   WS.Activate;

> --
> Deborah Pate (TeamB) http://delphi-jedi.org

>   Use Borland servers; TeamB don't see posts via ISPs
>   http://www.borland.com/newsgroups/genl_faqs.html

Other Threads