CODEWITHSUNDEEP
vbadelphiexcelado
Yordan Yanakiev
Yordan Yanakiev

Reputation: 2604

How to read the value of a specific cell from MS Excel with ADO query?

I am trying to extract data from specific cell from MS Excel worksheet.

Select [A1] from [Sheet1$]

does not work.

this is my connection string if it is from any help.

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;Data Source=c:\clients.xlsx;
Mode=Share Deny None;
Extended Properties="Excel 12.0 Xml;
HDR=yes";
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=37;
Jet OLEDB:Database Locking Mode=0;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don''t Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;
Jet OLEDB:Support Complex Data=False';

Upvotes: 0

Views: 2675

Answers (3)

Fionnuala
Fionnuala

Reputation: 91366

If you have headers, you select the name of the header, if you do not, the first column is F1, followed by F2 and so on. You can experiment by using SELECT * ... and then looking up the field names.

Upvotes: 2

Ken White
Ken White

Reputation: 125708

You've said that the data has headers (using HDR=YES; in your connection string), so you need to use the column name from that header row. For instance, if you have a sheet such as this:

    A    B
1 CODE  DESCRIPTION
2 100   This is an item description
3 200   This is another item

Your SELECT statement would be

SELECT Code, Description FROM [Sheet1$] WHERE [Code] = 100

The query above would return

Code  Description
100   This is an item description

If you need a specific cell from the resulting column, you can move to it after the query:

ADOQuery1.MoveBy(25);         // Move to row 25 of the results
DescriptStr :=  ADOQuery1.FieldByName('Description').Value;

Note that using ADO may not be the best way to access a single cell. Automation, for instance, would be much easier:

// You'll need to add ComObj to your uses clause for CreateOleObject
procedure TForm2.Button2Click(Sender: TObject);
var
  XLS: Variant;
  Range: Variant;
  Description: String;
begin
  XLS := CreateOleObject('Excel.Application');
  try
    XLS.WorkBooks.Open('C:\ExcelDocs\MyFile.xls');

    // Retrieve the cell
    Range := XLS.ActiveWorkBook.WorkSheets[1].Range['B2'];

    // Read its content
    Description := Range.Value;
    ShowMessage(Description);  // Displays 'This is an item description'
  finally
    Range := null;             // Release reference

    XLS.Quit;                  // Close Excel application
    XLS := null;               // Release reference
  end;
end;

Upvotes: 4

LHristov
LHristov

Reputation: 1123

Are you sure ADO is the best way to get the value of specific cell? ADO treats excel spreadsheets like sql tables and your question is similar to 'how to get record #5 from sql server table'.

Better try OLE automation. There you can easily address single cell or a range of cells. Here is an example from Microsoft.

Upvotes: 4

Related Questions