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 spreadsheet
has a macro that fires on startup, and as soon as I try to access the
spreadsheet 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\TargetSpreadsheet.xls" "Excel 8.0; " SELECT * FROM
["C:\Dump\values.xls"].[Sheet1$]"';
ADOQuery1.SQL.Text := 'INSERT INTO [Sheet1$] IN
"C:\Dump\TargetSpreadsheet.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
|
