+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    ~@%.com
    Guest

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

  2. #2
    Tom Ogilvy
    Guest

    Re: 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" <~@%.com> wrote in message
    news:[email protected]...
    > 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
    > ----------------------------------------------




  3. #3

    Re: 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. #4
    Tom Ogilvy
    Guest

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


    <[email protected]> wrote in message
    news:[email protected]...
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1