Delete Excel rows/sheets

[Rob Oldfield]

Using OleDB to write data to an Excel spreadsheet(*), is there really no way
to delete existing data without resorting to automation?

*... where the spreadsheet contains other sheets that refer to the data I
want to insert, so can't just overwrite the entire file.

 

[Linda Liu[MSFT]]

Hi Rob,

Based on my understanding, you'd like to delete data in a Microsoft Excel
Workbook via OLEDB. If I'm off base, please feel free to let me know.

You are more restricted in deleting Excel data than data from a relational
data source. In a relational database, "row" has no meaning or existence
apart from "record"; in an Excel worksheet, this is not true.

You can delete values in fields (cells). However, you cannot:

1. Delete an entire record at once or you receive the following error
message:
Deleting data in a linked table is not supported by this ISAM.

You can only delete a record by blanking out the contents of each
individual field.

2. Delete the value in a cell containing an Excel formula or you receive
the following error message:
Operation is not allowed in this context.

3. You cannot delete the empty spreadsheet row(s) in which the deleted data
was located, and your recordset will continue to display empty records
corresponding to these empty rows.

For more information on how to use ADO.NET to retrieve and modify records
in an Excel Workbook, please refer to the following KB article:
http://support.microsoft.com/kb/316934/EN-US/

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Rob Oldfield]

Thanks for that. I was aware of the limitations that you point out. My
question is really whether there is any way around it without resorting to
automation.

As an example of what I would like to do...

Export data from .net to an Excel spreadsheet (which will set up a named
ranged in the sheet)

Later on, reexport a modified batch of data (perhaps with fewer records) to
the same file, so that the named range remains valid (i.e. possibly shorter
than it was after the first export).

The easiest way that I can think of would simply be to delete the sheet from
the file before reexporting, but I haven't found a way of doing that (without
using automation).

 

[Linda Liu[MSFT]]

Hi Rob,

Thank you for your prompt reply! I understand your scenario better now.

In fact, we could use the "drop table" SQL statement to drop a sheet from
an Excel workbook. However, this command won't delete the sheet actually,
instead it clears the content of the sheet.

So in my opinion, there may not be a way to delete a sheet from an Excel
workbook without using automation.

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Rob Oldfield]

Thanks for that. It does actually solve the problem. I'd tried using DROP
TABLE before but hadn't taken it far enough. Even though that leaves the
named range as it was before the data is removed, when you then rerun the
CREATE TABLE and INSERT INTO commands, it allows you to do so, and you end up
with the sheet repopulated and the named range the correct size. Job done
(at least until my lusers tell me that they want to append thing like notes
to certain cells anyway).

 

[Anjan Kar]

Hi Rob,

Can you please provide the code snippet of clearing of sheet content in an Excel file and repopulating it with Create and Insert statement...

I am also facing the same problem...I want to clear a Sheet's content and repopulate it..

Thanks in ADV