Board index » delphi » Excel sheet name

Excel sheet name

Hi,

Is there anyway of identifying what sheets are available within an Excel
spreadsheet that has been opened with an ADODataset?

At the moment the sheet can be set with the 'commandtext' property, if I
look in the object inspector this property is a pull down menu with all the
sheets listed - however I am having trouble getting to these sheet names
programatically as the commandtext property is a widestring, not a
stringlist...

I use this function to open the spreadsheet with an ADODataset:

function TMyClass.OpenSpreadsheet(filename: string; sheetName : string) :
boolean;
begin
  Result := True;
  try
    FADODataSet1.Active := False;
    FADODataSet1.ConnectionString :=  '
Provider=Microsoft.Jet.OLEDB.4.0;User ID=admin;' +
      'Data Source=' + filename + ';' +
      'Mode=Read;Extended Properties=Excel 8.0;' +
      'Persist Security Info=False';
    FADODataSet1.CommandType := cmdTable;

    FADODataSet1.CommandText := sheetname + '$';

    FADODataSet1.Active := True;
  except
    on E : Exception do Result := False;
  end;
end;

However I need to poll the spreadsheet and identify all the sheetnames so I
can cycle through them. Any ideas?

Thanks

Matt

 

Re:Excel sheet name


Hi Matt

Use a TADOConnection to connect to your spreadsheet and hook your
TADODataSet's Connection property to it. You can then use GetTableNames on
the TADOConnection to obtain access to the sheet names.

HTH

Paul

Quote
"Matt" <matthew.la...@NOSPAMstcenergy.com> wrote in message

news:3da2d082@newsgroups.borland.com...
Quote
> Hi,

> Is there anyway of identifying what sheets are available within an Excel
> spreadsheet that has been opened with an ADODataset?

> At the moment the sheet can be set with the 'commandtext' property, if I
> look in the object inspector this property is a pull down menu with all
the
> sheets listed - however I am having trouble getting to these sheet names
> programatically as the commandtext property is a widestring, not a
> stringlist...

> I use this function to open the spreadsheet with an ADODataset:

> function TMyClass.OpenSpreadsheet(filename: string; sheetName : string) :
> boolean;
> begin
>   Result := True;
>   try
>     FADODataSet1.Active := False;
>     FADODataSet1.ConnectionString :=  '
> Provider=Microsoft.Jet.OLEDB.4.0;User ID=admin;' +
>       'Data Source=' + filename + ';' +
>       'Mode=Read;Extended Properties=Excel 8.0;' +
>       'Persist Security Info=False';
>     FADODataSet1.CommandType := cmdTable;

>     FADODataSet1.CommandText := sheetname + '$';

>     FADODataSet1.Active := True;
>   except
>     on E : Exception do Result := False;
>   end;
> end;

> However I need to poll the spreadsheet and identify all the sheetnames so
I
> can cycle through them. Any ideas?

> Thanks

> Matt

Other Threads