delete row from xls spreadsheet

[Tracey]

Hi

I have been following this tutorial:

http://delphi.about.com/od/database/l/aa090903a.htm

on Accessing data from M$ Spreadsheets.

There is a comment in the delete paragraph:

Deleting rows
Deleting the selected "record" is somehow problematic. You cannot delete the record in Excel data source, since rows in Excel are not compatible with rows in traditional (relation) databases. What you can do, is to clear the contents of every cell (field) the row contains.
Caution: If you try to delete an entire record (AdoQuery1.Delete or nbDelete on the DBNavigator), an error "Deleting data in a linked table is not supported by this ISAM." will stop the operation. If, ot the other hand, you try to delete a value in the "Formula" cell, "Operation is not allowed in this context." will be raised.

Of course he is quite correct. What i would like to know is how to clear the contents of every cell (field) the row contains.

Can anyone here help?

Tracey
Remember... True happiness is not getting what you want...

Its wanting what you have got!

 

[Pikkunero]

You could do this without ADO connection to the worksheet. Use Olevariant to connect to the excel file and one of it's worksheets. Or if you have the Servers tab in your delphi you can connect with them to the xls file.

After doing that you can go through the rows and columns and set the cell value to null for example.

Here's a small example of how to go through the columns using ExcelWorkSheet from the servers tab.

  for i := 1 to ExcelWorksheet1.Columns.Count do
  begin
    ExcelWorksheet1.Cells.Item[YourRowNr, i].Value := null;

  end;

It shouldn't be much different if you use Olevariant.

 

[svanels]

I also would use a server component. You could try to cath the comands with the macro editor in excel

The VBA code is :
1)(Selecting the entire row with the mouse and press delete)

Rows("1:1").Select
Selection.Delete Shift:=xlUp

2) Using delete on the menubar of excel

Range("A1").Select
Selection.EntireRow.Delete

Combining the two fragments I got the Delphi equivalent:

range['a1','a1'].EntireRow.Delete(xlUp);

For more info check out FAQ
How to export data to Excel faq102-1562

I used the example to delete the first row.

Steven van Els
SAvanEls@cq-link.sr

 

[Tracey]

Yeah, that kind of takes me down an entirely different path to the one i have taken. Loading records from an xls file is so simple with ADO, even editing them. I have since removed the proposed ability to delete records from within my application.

Since this app will only be used by one person, who works in the same office as me, it would simply be more cost effective for her to open the file in excel to delete any rows.

Cheers anyway guys

Tracey



Tracey
Remember... True happiness is not getting what you want...

Its wanting what you have got!