Accessing and managing MS Excel sheets with Delphi - Transfer data to: MS Excel

transferring data from other sources (in your Delphi application) to an Excel workbook.

From Acees to Excel over Delphi using ADO

you can use SQL statements to insert (copy) data from any Jet compliant data source to any (compatible) data destination.
If this destination is an Excel workbook, Delphi (using ADO) enables you to transfer you "custom" data to Excel and even create a new workbook, if needed.

you can use SQL statements to insert (copy) data from any Jet compliant data source to any (compatible) data destination.
If this destination is an Excel workbook, Delphi (using ADO) enables you to transfer you "custom" data to Excel and even create a new workbook, if needed.

(TAdoQuery). We'll be accessing the sample QuickiesContest.mdb

along with the link between AdoConnection2 and AdoQuery2 (add this code to the end of the Form1 OnCreate even handling procedure):

//connect to an Access database to send the data to Excel AdoConnection2.LoginPrompt:=False; AdoConnection2.ConnectionString:= 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\!Gajba\About\QuickiesContest.mdb; Persist Security Info=False'; AdoQuery2.Connection:=AdoConnection2;

Now, we'll need two more buttons: Button1 (TButton) will be used to insert data from Access to Excel, and Button2 (TButton) will be used for data copying purposes.

Insert Into ... Excel

When transferring data to an existing sheet, the column headings must already be present.

Here's an example of appending the values from three fields in the Articles table (Access database) to the Sheet2 in our test workbook:

procedure TForm1.Button2Click(Sender: TObject); var sAppend : string; begin sAppend := 'INSERT INTO [Sheet2$] IN "' + Edit1.Text + '" "Excel 8.0;" SELECT AuthorEmail, Title, Description FROM Articles'; AdoQuery2.SQL.Text:=sAppend; AdoQuery2.ExecSQL; end;

go to that Excel workbook, select Sheet2, and see for yourself: the data is transferred

Select Into ... Excel

to copy data from Access to Excel, and create a new sheet (and even a new workbook) along the way, you could try the next code:

procedure TForm1.Button1Click(Sender: TObject); var sCopy : string; begin sCopy := 'SELECT * INTO ["Excel 8.0;Database=' + Edit1.Text + '"].[SheetAuthors] FROM Authors'; AdoQuery2.SQL.Text:=sCopy; AdoQuery2.ExecSQL; end;