Using ADODataset component to open an Excel Spreadsheet

I'm using ADOdataset component to view an Excel Spre开发者_如何学运维adsheet on a Delphi form.

The component requires that the CommandText property be set to the name of a spreadsheet in the workbook being opened.

How can I set this property to open the first spreadsheet in the workbook regardless of the name?

procedure TForm1.BitBtn1Click(Sender: TObject); 
   var   XLSFile, CStr : string; 
begin    
   if OpenDialog1.Execute() then 
   begin
       XLSFile := OpenDialog1.FileName;

       CStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='   
           +XLSFile+'; Extended Properties=Excel 8.0;Persist Security Info=False';

       ADODataSet1.Active := False;    
       ADODataSet1.ConnectionString := Cstr;    
       ADODataSet1.CommandText := ??????????; 
       ADODataSet1.Active := True;
    end;    
 end;

You can't, you have to know the name of the page. Use a TADOConnection to connect to the work book so that you can call GetTableNames to retrieve the names of pages. There's an example here. Then you can set the Connection property of an ADO data set or an ADO query to the connection object and run a query.

While I am sure someone will provide an answer to your question, I would like to suggest, if it is at all appropriate, that you consider an alternative approach. Take a look at NativeExcel. For your immediate need, you can access the spreadsheets by sheet name or index. The first sheet is found as Book.Sheets[1]. The developer maintains a very nice support document delivered as a help file or online here. I have used the library for a couple of years and found it to be very stable and powerful.

Add property:

ADODataSet1.CommandType := cmdUnknown;