Update cell query for Excel ADO from Delphi

Question

I have the following code to open my Excel Worksheet, using TADOConnection and TADOQuery:

conExcel.ConnectionString := 'Provider=Microsoft.JET.OLEDB.4.0;Data Source="D:\temp\file.xls";Extended Properties="Excel 8.0;HDR=No"';
conExcel.Connected := true;

When I run the following code:

queryExcel1.SQL.Text := 'SELECT * FROM [Hoja1$]';
queryExcel1.Open;
while not queryExcel1.eof do
begin
  showmessage(queryExcel1.Fields.Fields[0].AsString);
  queryExcel1.Next;
end;

I get each line of the file, so it is working ok, but I want to update a cell. I am using this code:

queryExcel2.SQL.Text := 'UPDATE [Hoja1$] SET F1 = 555555';
queryExcel2.ExecSQL;

Which somehow updates every "A" cell on the worksheet to 555555, but what I really want is to just set the A1 cell to 555555. Any hints on how to include the WHERE section of the query. Thanks.

I have tried queryExcel1.SQL.Text := 'SELECT * FROM [Hoja1$A1:A10]'; even in the select statement and it fails with error: 'Parameter object is improperly defined' — JoeGalind, Aug 21 '13 at 17:03

score 1 · Accepted Answer · answered Aug 21 '13 at 17:11

If you want to use an Adoquery you will have to set ParamCheck to false, since you will have to use : for the range you want to update.
An other option would be to use the connection directly.
In the example below the range B2:B2 is used with F1 which is the default name of Jet for the first column. (B2 is the cell we want to update)

  Q.SQL.Text := 'UPDATE [Hoja1$B2:B2] SET [F1] = 555555';
  Q.ExecSQL;
  // or:
  //AdoConnection1.Execute('UPDATE [Hoja1$B2:B2] SET [F1] = 555555');

Setting paramcheck to false did the trick! Working perfectly now. — JoeGalind, Aug 21 '13 at 17:24

Update cell query for Excel ADO from Delphi

1 Answers1

Linked