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 ---------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make text data export to excel in text format.
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 "Johnny" wrote in message ... 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 ---------------------------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make text data export to excel in text format.
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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make text data export to excel in text format.
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 wrote in message ups.com... 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 wrote: 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 |
Reply |
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 |