Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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;ReadO nly=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 ---------------------------------------------- |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to export plain text from web site to Excel without data conve | Excel Discussion (Misc queries) | |||
How do I export data in text format with minimum of spaces? | Excel Discussion (Misc queries) | |||
How can I export text from excel autoshapes to a text file? | Excel Discussion (Misc queries) | |||
Export SQL Server data to EXCEL cause all column become TEXT | Excel Programming | |||
Export to excel from ASP: Text data interpretation issue | Excel Programming |