I have an Excel spreadsheet that I am exporting data from  SQL server via DTS. the column in SQL is varchar and the column in Excel is text. But after the
export, in Excel the data is stored as "Number Stored at text" instead of text stored as text.

The same thing happens when I use VB to add the data to Excel. here is the VB Code.

For some reason Excel is looking at the contents of the data and if all fields in a colun consist of all
Excel insist on foramtting as the data "number stored as text"

Thanks in advance for help with this problem.

Johnny

Dim mSql As String
Dim mcnn As New ADODB.Connection
Dim mcnnExcel As ADODB.Connection

Set mcnnExcel = New ADODB.Connection
With mcnnExcel
   
    .Provider = "MSDASQL"   ' ODBC dsnless connection
    .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
                        "DBQ=D:\VB_Projects\Excel\1.xls;Extended Properties=Excel 2002 (XP);IMEX=1;FirstRowHasNames=1;MaxScanRows=1;ReadOnly=False;"

    .Open
End With

Set mcnn = New ADODB.Connection
With mcnn
    .Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;PWD=admin;" & _
          "Initial Catalog=EXCEL; Data Source=HP-A350Y"
End With

Dim oRS As New ADODB.Recordset
oRS.Open "Select * from [Master Form$]", mcnnExcel, adOpenKeyset, adLockOptimistic

'get recordset from Sql Server
mSql = "select * from texcel;"
meof = fn_810OpenRecordset(mSql, mcnn)

'add the records to Excel
Do While Not (mAdoRs.EOF)
       oRS.AddNew
       For i = 0 To 4  'these fields need to BE TEXT stored as text
       oRS.Fields(i).Value = mAdoRs.Fields(i).Value
       Next
       For i = 5 To 15   ' these fields need to be numeric
       oRS.Fields(i).Value = mAdoRs.Fields(i).Value
       Next
       oRS.Update
       mAdoRs.MoveNext
Loop