How to use ADO to insert data into Excel??

Hello All!

I'm trying to get around a problem when reading data from Excel using ADO. Integer values aren't coming through - I Googled this it seems to be an issue with Excel - whatever data format is found in the first 8 rows is assumed to be the data format for the remaining rows. This means that the text in my first rows throws-out the integer values in rows further down (they come through as NULL).

One possible solution (I think) is to add '0.00' values to those first 8 rows. The trouble is - I can't do it and so need somebody's help!

I first tried using OLE to insert the values, but the target spread*** has a macro that fires on startup, and as soon as I try to access the spread*** with OLE, the macro jumps to life - which I don't want.

I then tried ADO, and I can read data without any issues (no macro fire-up), but I'm unable to insert data. I'm using the ADO Query component in Delphi 6. Ideally, I don't want to have to select the update data from another source - I'd like to just specify a cell range and hard-code "0.00" (or whatever).

I've tried the following combinations based on examples I have found online - but I'm clearly missing something as I can't get them to work...


ADOQuery1.SQL.Text := 'INSERT INTO [Excel 8.0;C:\dump\TargetSpreasheet.xls;HDR=NO;].[Sheet1$] (F4) "0.00"';

ADOQuery1.SQL.Text := 'INSERT INTO [Excel 8.0;C:\dump\TargetSpreasheet.xls;HDR=NO;].[Sheet1$] (F4) 0.00';

ADOQuery1.SQL.Text := 'SELECT "Needed for ADO access." INTO ["Excel 8.0;Database=' + 'C:\dump\TargetSpreasheet.xls' + '"].[Sheet1$]';

ADOQuery1.SQL.Text := 'SELECT * INTO ["Excel 8.0;Database=C:\Dump\TargetSpreadsheet.xls"].[Sheet1$] IN ["C:\Dump\values.xls" "EXCEL 8.0;"]';

ADOQuery1.SQL.Text := 'SELECT * INTO ["Excel 8.0;Database=C:\Dump\TargetSpreadsheet.xls"].[Sheet1$] IN ["EXCEL 8.0;Database=C:\Dump\values.xls"].[Sheet1$]';

ADOQuery1.SQL.Text := 'SELECT * INTO ["C:\Dump\TargetSpreadsheet.xls"].[Sheet1$] IN ["C:\Dump\values.xls"]';

ADOQuery1.SQL.Text := 'INSERT INTO [Sheet1$] IN "C:\Dump\TargetSpread***.xls" "Excel 8.0; " SELECT * FROM ["C:\Dump\values.xls"].[Sheet1$]"';

ADOQuery1.SQL.Text := 'INSERT INTO [Sheet1$] IN "C:\Dump\TargetSpread***.xls" "Excel 8.0; " SELECT * FROM ["C:\Dump\values.xls"].[Sheet1$]" "Excel 8.0; "';

ADOQuery1.SQL.Text := 'INSERT INTO [Sheet1$] IN "C:\Dump\TargetSpreadsheet.xls" "Excel 8.0; " SELECT * FROM [Sheet1$] in "C:\Dump\values.xls" "Excel 8.0; "';


My head hurts!

If anyone out there with a functioning brain can help, please do!

Many thanks in advance,

TOAD
.

Quantcast