(Belinda) wrote ...

I am reading data from a SQL Server table using ADO and as I read the
recordset I place each recordset field into a Excel cell. But
sometimes when I read a field in the SQL Server which is of type
nvarchar the data in the SQL table is
as follows:

000003710
000003720

but in Excel this is displayed by my ADO read program with the leading
zeros chopped as follows:

3710
3720

how can I set the Excel cell format programmatically based on the data
type of the SQL table field please advise.

I too use Excel as a front end to SQL Server (among other databases).
Rather than a worksheet, I use a grid on a userform. Specifically the
Microsoft DataGrid Control 6.0 (SPS) (OLEDB), or MSDATAGRD.OCX for
short.

The good thing about it is you can associate an ADO recordset with its
DataSource property. The result is the data types display correctly
and any changes (insert/delete/amend row) in the grid get
automatically written back to the recordset.

The bad news is It doesn't ship with Excel (I think mine came with
Visual Studio 6.0). Some say the reason for this is that an Excel
worksheet makes an adequate enough grid <g. However, for these
purposes, a worksheet is just too flexible. In addition to the
formatting issue, there aren't enough worksheet events to adequately
trap changes (insert/delete/amend row). The best that can be done is
to do a 'before and after comparison', allowing for the fact the user
is free to do 'daft' things such as sort/filter the data,
delete/insert columns, etc.

To actually answer your question, you could write your own function to
return a cell format based on the recordset's field value for any
columns you feel Excel doesn't handle correctly. Bear in mind that an
Excel cell format has four elements for positive numbers, negative
numbers, zero and text respectively (see
http://support.microsoft.com/default...s/default.asp).
I'd suggest using ADO's DataTypeEnum as a template, something like

Public Function GetFormat( _
ByVal DataType As ADODB.DataTypeEnum _
) As String

Select Case DataType

Case adEmpty
GetFormat = <<format goes here
Case adSmallInt, adInteger
GetFormat = <<format goes here
Case adSingle, adDouble
GetFormat = <<format goes here
Case adCurrency
GetFormat = <<format goes here

....

Jamie.

--