How to make text data export to excel in text format.

[~]

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
--

 

[Tom Ogilvy]

numbers stored as text is a warning, not a data type.

if the cells contain numbers and they are stored as text, then in later
versions of excel, you can get this indication. The only alternative is to
store them as numbers, but it sounds like you want them stored as text and
they are.

--
Regards,
Tom Ogilvy

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
--
----------------------------------------------
Posted with NewsLeecher v3.0 Final
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
----------------------------------------------

 

[lovely_angel_for_you]

Hi,

I am in a similar situation. However, I am sending the numbers from my
VB application to Excel and instead of being stored as numbers they are
getting stored as Text. And if we check the excel, it stay the number
are stored as text and gives the sae rectangle to fix it.

Any help on how we can store the number as numbers directly to excel
rather than changing the fields in excel after they have been entered.

Any idea will be appreciated.

Thanks & Regards
Lovely

 

[Tom Ogilvy]

How are you sending your numbers to excel?

If your are using some standard method, then you may just have to fix them
after they get there

Dim rng as Range
set rng =Range(cells(1,1),cells(rows.count,1))
rng.Numberformat = "general"
Range("IV1").Value = 1
Range("IV1").copy
rng.pasteSpecial xlValues, xlMultiply
Columns(256).Delete
ActiveSheet.UsedRange

--
Regards,
Tom Ogilvy

Hi,

I am in a similar situation. However, I am sending the numbers from my
VB application to Excel and instead of being stored as numbers they are
getting stored as Text. And if we check the excel, it stay the number
are stored as text and gives the sae rectangle to fix it.

Any help on how we can store the number as numbers directly to excel
rather than changing the fields in excel after they have been entered.

Any idea will be appreciated.

Thanks & Regards
Lovely