Deleting a tabsheet from an Excel WorkSheet with ADO

[NicolasDeMarco]

First: sorry for my english:

I create a new sheet into an Excel Worksheet using an ADOConnection (pointing the file with Microsoft Jet OLE DB Provider).

Connection.ConnectionString :=
'Provider=Microsoft.Jet.OLEDB.4.0;' +
'Extended Properties="Excel 8.0;HDR=Yes;";' +
'Data Source="' + Trim(FileName) + '"';

I use an empty Excel Worksheet wiht an only tabsheet. Of course I cannot create an Excel Worksheet with no tabsheets.

The way to create the NEW sheet was executing a TADOQuery.Execute "CREATE TABLE Tablename, etc....". Then I create the rows executing a TADOQuery.Execute "INSERT INTO Tablename (col1, col2, col3) VALUES (value1, value2, value3)". That's all OK.

When I tried to delete the original sheet (Sheet1) using the following TADOQuery.Execute "DROP TABLE Sheet1". I received the response "Table does not exists".

I get the contents of the database (worksheet) with the following code, but it does not work:

// Code
List := TStringList.Create;
Connection.GetTableNames(List, False);

ADOQry.Close;

for i := 0 to List.Count - 1 do begin
if (Trim(List) <> 'Tablename') then
begin
ADOQry.SQL.Text := 'DROP TABLE [' +
StringReplace(Trim(Listatablas), '$', '', [rfReplaceAll]) + ']';
ADOQry.Execute;
end;
end;
List.Free;
// I tried with ('[',']') and without ('[',']')

If somebody has an answer I will appreciate.

Nicolas

 

[Guest_imported]

I am trying similar things. If you try &quot;Drop table Sheet1$&quot;, it finds the table, but does not delete it.

Any thoughts?

 

[lespaul]

Are you restricted to using ADO? I usually do something like this (most of my Delphi to MS products has been word, but I've done it similarly with excel in another program):

var
Word, wrdDoc : variant
begin
Word := CreateOleObject('Word.Application');
Word.Visible := False;
wrdDoc := Word.Documents.Open(Dir:/DocName.doc);
//or if creating new document based on template then
//wrdDoc := Word.Documents.Add(Dir:/TempName.dot);
//Then you can manipulate the Word Document
end;

I have Delphi code that creates a table on the word document, sets the column widths individually, draws or removes borders from the table, sets the fonts, and inserts data from an array into each cell and then prints it.

For excel:
var
Excel, exlsht : variant;
begin
Excel := createobject('Excel.application');
Excel.visible := False;
excelsht = excelapp.Workbooks(&quot;FileName1&quot.Worksheets(&quot;Sheet1&quot;

//again Add for templates, Open for workbooks, and
//I believe you leave add() if you want a blank new workbook
//excelapp.Workbooks.add(&quot;C:\FileName.XLT&quot;
//when using templates you have to add the 1 to the end of
//the filename. If you use Open, just pass the exact file
//name to the sheet and if the tabs are named, pass the
//name to the Worksheets parameter
end;

As I said, most of my Delphi experience in this area has been with Word. I have found in previous applications though that Excel can do much more in the calculation process if I write VBA macros and modules that I call externally. If you are creating this table in order to do queries on the transmitted information, look into the DBfunctions that Excel offers. Allows very database like manipulations of criteria (once you get used to the wierd syntax).

Warning: the syntax of VBA calls from Delphi is just a little bit different that what you will find in the VBA &quot;Help&quot;, but it is doable.

I'm assuming that you have run a previous query that contains the information that you want to put into excel. Instead of your UpdateQuery, you can transfer information directly into the Excel cells from Delphi.

In order to close the file without being prompted for the &quot;Do you want to save changes&quot;, the Close(0) call works for Word and in Excel you set DisplayAlerts = False. This way you don't have to drop a table, since all you really have is a spreadsheet object that you close without saving.

I hope that this will help you. Since I don't know why you are creating a table, it's difficult to tell if this is a workable solution for you. Good luck. Feel free to email me if you would like any more information.



Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned