Win prizes by sharing code! Do you have some Delphi code you want to share? Are you interested in winning a prize for your work? Delphi Programming Quickies Contest

More of this Feature • Page 1: Connect to: MS Excel • Page 2: Creating an Excel spreadsheet editor • Page 3: Retrieving the data from Excel • Page 4: Excel field (column) types • Page 5: Modify Excel sheets • Page 7: Full source code

Join the Discussion "Post your views, comments, questions and doubts to this article." Discuss!

Related Resources • A Beginners Guide to ADO programming • Automation with Excel and Delphi

Ok, we've managed to set up, and get the Delphi version of the Excel spreadsheet editor up and running. In the process we've talked about connecting to Excel (eg. the ConectionString), about Excel columns and data types.

What we are up to now is transferring data from other sources (in your Delphi application) to an Excel workbook. Back in the beginning of this article, we've concluded that many developers find appropriate to transport their data into an Excel workbook for analysis purposes.

From Acees to Excel over Delphi using ADO

To test the truth of the above statement, we'll adopt our Excel spreadsheet editor project to be able to connect to an Access database - we'll use as the data source.

Go "back" to Delphi, and add two more components on the Form1: AdoConnection2 (TAdoConnection) and ADOQuery2 (TAdoQuery). We'll be accessing the sample QuickiesContest.mdb Access database introduced and described in the "Adding components to a DBGrid" article.

This is how the connection to the Access database might look like, 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

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;

Note: the above code appends the values of the fields AuthorEmail, Title, Description from the Articles table to an existing Excel workbook, the data is appended in a sheet named Sheet2.

Caution: You must be very careful with the quotes (") usage or you might end up with the "Parameter object is improperly defined. Inconsistent or incomplete information was provided" error. Furthermore, if you try to append the data, and one of the column headings does not exist (like "Title"), you'll receive the "The INSERT INTO statement contains the following unknown field name: 'Title'. Make sure you have typed the name correctly, and try the operation again." error message.

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

Select Into ... Excel

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;

Note: the code above creates a workbook with the name specified in Edit1.Text property, if a workbook with the specified name does not already exist. The Select Into statement copies all the data from the Authors table (Access) to a newly created sheet called SheetAuthors. Furthermore note that you do not need to add the $ sign at the end of the sheet name.

Caution: You must be very careful with the quotes (") usage or you might end up with the "Parameter object is improperly defined. Inconsistent or incomplete information was provided" error. What's more, if you try to copy the data, and the workbook is already open, an error message "The Microsoft Jet database engine could not find the object 'SheetAuthors'. Make sure the object exists and that you spell its name and the path name correctly" will be thrown.

That does it!

As always, if you have any questions or comments, please post to the Delphi Programming Forum.

Next page > Excel Excel spreadsheet editor: Delphi source code > Page 1, 2, 3, 4, 5, 6, , 7