Export Data to Excel Programmatically

8 Jul 2005 3:05 PM

Mike

I am trying to export data from SQL 2000 into an Excel spreadsheet that needs
to be in a certain format. The creation of the excel file will be called from
a web app and there will be any number of rows to load. I will then edit the
file and re-import the data into SQL.
I am currently loading the data using OPENDATASOURCE which allows me to load
the data into a preformatted file (hidden columns and headings). The problem
is that all the numeric fields are formatted as text and display that in the
spreadsheet. If I try and edit the cell it is converted to a numeric and I
end up with one numeric column amongst all the textual columns. When I try
and re-import the file I end up returning the converted data only.
Is there a way I can populate the excel with the correct data format?
Alternatively does anyone know how to populate Excel from SQL without
needing to call DTS or command line commands.
Thanks in advance

8 Jul 2005 6:29 PM

Kevin Bowker

I have a VB (6) class written to create Excel files using ADO 2.x that I'd be
happy to share if it will help any. I make use of this class with COM/COM+
typically on a server without Excel/Office installed. The class allows for
data types on the column and limited formatting of cells. If it sounds like
any help, I can post it here or you can email me at ke***@kbware.com if
posting that code to this group would be considered off-topic.

Show quote

"Mike" wrote:

> I am trying to export data from SQL 2000 into an Excel spreadsheet that needs
> to be in a certain format. The creation of the excel file will be called from
> a web app and there will be any number of rows to load. I will then edit the
> file and re-import the data into SQL.
> I am currently loading the data using OPENDATASOURCE which allows me to load
> the data into a preformatted file (hidden columns and headings). The problem
> is that all the numeric fields are formatted as text and display that in the
> spreadsheet. If I try and edit the cell it is converted to a numeric and I
> end up with one numeric column amongst all the textual columns. When I try
> and re-import the file I end up returning the converted data only.
> Is there a way I can populate the excel with the correct data format?
> Alternatively does anyone know how to populate Excel from SQL without
> needing to call DTS or command line commands.
> Thanks in advance
>