J
Joel Gordon
Hi,
I'm wanting to write an in memory ADO.Net Datatable to a
worksheet
in an Excel spreadsheet. If the worksheet exists I would like to
overwrite all the existing data in the worksheet.
I've successfully used the Jet OLE DB Provider to create a new Excel
worksheet (using a CREATE TABLE) and populate it. But I haven't been
able to overwrite data in an existing worksheet.
In an old posting (Oct 2002) to microsoft.public.data.ado (Subject:
Delete records in Excel with ADO from VB) it was mentioned that Excel
automation is needed to delete empty rows cleanly. But it also
mentioned
that you can execute a DROP TABLE and start over.
I have tried using DROP TABLE which deletes all the data in a worksheet,
however it also seems to delete the column headers which means when I
use the InsertCommand on an OleDbDataAdapter to write the dataTable to
Excel no data gets written.
I was hoping DROP TABLE would actually delete the worksheet (rather than
just it's contents) so that I could then re-create the worksheet (using
CREATE TABLE).
Question :
------------
Am I doing something wrong with my InsertCommand, or is there some way
I can delete the worksheet itself or delete the contents of the
worksheet and still leave the column headers intact ?
I would like to avoid using Excel automation if possible, since if I
don't use it Excel does not need to be installed, versioning issues can
be avoided and I presume that using the Jet OLE DB Provider is faster
than using automation.
Thanks in advance,
Joel Gordon.
I'm wanting to write an in memory ADO.Net Datatable to a
worksheet
in an Excel spreadsheet. If the worksheet exists I would like to
overwrite all the existing data in the worksheet.
I've successfully used the Jet OLE DB Provider to create a new Excel
worksheet (using a CREATE TABLE) and populate it. But I haven't been
able to overwrite data in an existing worksheet.
In an old posting (Oct 2002) to microsoft.public.data.ado (Subject:
Delete records in Excel with ADO from VB) it was mentioned that Excel
automation is needed to delete empty rows cleanly. But it also
mentioned
that you can execute a DROP TABLE and start over.
I have tried using DROP TABLE which deletes all the data in a worksheet,
however it also seems to delete the column headers which means when I
use the InsertCommand on an OleDbDataAdapter to write the dataTable to
Excel no data gets written.
I was hoping DROP TABLE would actually delete the worksheet (rather than
just it's contents) so that I could then re-create the worksheet (using
CREATE TABLE).
Question :
------------
Am I doing something wrong with my InsertCommand, or is there some way
I can delete the worksheet itself or delete the contents of the
worksheet and still leave the column headers intact ?
I would like to avoid using Excel automation if possible, since if I
don't use it Excel does not need to be installed, versioning issues can
be avoided and I presume that using the Jet OLE DB Provider is faster
than using automation.
Thanks in advance,
Joel Gordon.