Using ADODataset component to open an Excel Spreadsheet

Question

I'm using ADOdataset component to view an Excel Spreadsheet 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;

score 7 · Accepted Answer · answered Jul 18 '11 at 17:17

7

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.

answered Jul 18 '11 at 17:17

Sertac Akyuz

54,131
4
102
169

How do you deal with the TADODataSet always using the first row as field names? I actually have values and multi-cell spanning on the first line. – SiBrit Apr 09 '19 at 04:14
Nevermind. I found that you need to include HDR=NO in the extended properties of the connection string. https://support.microsoft.com/en-nz/help/278973/excelado-demonstrates-how-to-use-ado-to-read-and-write-data-in-excel-w – SiBrit Apr 09 '19 at 04:25

score 1 · Answer 2 · answered Jul 18 '11 at 15:49

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.

score -2 · Answer 3 · answered Nov 23 '15 at 12:20

-2

Add property:

ADODataSet1.CommandType := cmdUnknown;

answered Nov 23 '15 at 12:20

Jacek Kałabun

1

Using ADODataset component to open an Excel Spreadsheet

3 Answers3

Linked