+ Reply to Thread
Results 1 to 9 of 9

retain table formatting when writing excel table to a txt file

  1. #1
    deanop
    Guest

    retain table formatting when writing excel table to a txt file

    table in excel file is as follows:


    col b col c col d


    Black-Scholes Directly in a Excel Sheet


    Stock price S 61
    Strike price X 65
    Years to maturity T 0.25
    Risk-free rate r 0.08
    Volatility v 0.3


    d1 -0.215
    d2 -0.365


    European call value 2.527
    European put value 5.240


    here is code to write this table to a txt file:


    Sub writetabletotxtfile()
    Dim ExpRng As Range
    Dim ff As Integer
    Set ExpRng =3D Range("worksheet_to_text")
    'Set ExpRng =3D ActiveCell.CurrentRegion
    FirstCol =3D ExpRng.Columns(1).Column
    LastCol =3D FirstCol + ExpRng.Columns.Count
    FirstRow =3D ExpRng.Rows(1).Row
    LastRow =3D FirstRow + ExpRng.Rows.Count


    ff =3D FreeFile()


    Open "C:\Documents and Settings\xyz\Desktop\tabletote=ADxtfile.txt" For
    Output As ff
    Print #ff, ExpRng.AddressLocal()
    Print #ff, ExpRng.AddressLocal(RowAbsolut=ADe:=3DFalse,
    columnabsolute:=3DFalse)


    For r =3D FirstRow To LastRow
    For c =3D FirstCol To LastCol
    vdata =3D ExpRng.Cells(r, c).Text
    If c <> LastCol Then
    Print #ff, vdata; Chr(44);
    Else
    Print #ff, vdata
    End If
    Next c
    Next r
    Close ff


    End Sub


    here is txt file produced when running this code:


    $B$2:$E$18
    B2:E18
    ,,,,
    ,,,,
    S,61,,,
    X,65,,,
    T,0.25,,,
    r,0.08,,,
    v,0.3,,,
    ,,,,
    d1,-0.215,,,
    d2,-0.365,,,
    ,,,,
    ,2.527,,,
    ,5.240,,,
    ,,,,
    ,,,,
    ,,,,
    ,,,,
    ,,,,


    could u help me write this table retaining table spacing and
    formatting.=20


    thanks folks, deano


  2. #2
    deanop
    Guest

    Re: retain table formatting when writing excel table to a txt file

    Ok, I see how I goofed and have revised code ro read as follows:
    a range name of the excel table is defined as "worksheet_to_text"
    --------------------------------------------------------------------------------------------------------------
    Sub writetabletotxtfile()
    Dim ExpRng As Range
    Dim ff As Integer
    Set ExpRng = Range("worksheet_to_text")
    Firstcol = ExpRng.Columns(1).Column
    LastCol = Firstcol + ExpRng.Columns.Count
    FirstRow = ExpRng.Rows(1).Row
    LastRow = FirstRow + ExpRng.Rows.Count

    ff = FreeFile()

    Open "C:\Documents and Settings\XYZ\Desktop\tabletotextfile.txt" For
    Output As ff
    Print #ff, ExpRng.AddressLocal()
    Print #ff, ExpRng.AddressLocal(RowAbsolute:=False,
    columnabsolute:=False)

    For r = FirstRow To LastRow
    For c = Firstcol To LastCol
    Print #ff, Cells(r, c);
    Next c
    Print #ff, 'blank line
    Next r
    Close ff
    End Sub

    --------------------------------------------------------------------------------
    Here is the result txt file. see how recreated table line by line but
    still unable to get spacing right. plz help

    $B$2:$E$18
    B2:E18

    Black-Scholes Directly in a Excel Sheet

    Stock priceS 61
    Strike priceX 65
    Years to maturityT 0.25
    Risk-free rater 0.08
    Volatilityv 0.3

    d1-0.215089371482172
    d2-0.365089371482172

    European call value 2.52698589175614
    European put value 5.23989965669523


  3. #3
    deanop
    Guest

    Re: retain table formatting when writing excel table to a txt file

    See second post, table in excel is written to a text file line by line
    but still unable to get spacing right. plz help


  4. #4
    deano
    Guest

    Re: retain table formatting when writing excel table to a txt file

    plz folks, still looking for your help


  5. #5
    deano
    Guest

    Re: retain table formatting when writing excel table to a txt file

    plz folks, still looking for your help


  6. #6
    Tim Williams
    Guest

    Re: retain table formatting when writing excel table to a txt file


    "deano" <[email protected]> wrote in message
    news:[email protected]...
    > plz folks, still looking for your help
    >


    Your original message has disappeared from my listing, so I have no idea
    what question you had....

    Always helps to quote the post you're replying to.

    --
    Tim Williams
    Palo Alto, CA




  7. #7
    deano
    Guest

    Re: retain table formatting when writing excel table to a txt file

    could u help me write this table retaining table spacing and
    formatting,

    table in excel file is as follows:


    col b col c col d

    Black-Scholes Directly in a Excel Sheet

    Stock price S 61
    Strike price X 65
    Years to maturity T 0.25
    Risk-free rate r 0.08
    Volatility v 0.3


    d1 -0.215
    d2 -0.365


    European call value 2.527
    European put value 5.240

    -------------------------------------------------------------------
    here is code to write this table to a txt file:

    code ro read as follows:
    a range name of the excel table is defined as "worksheet_to_text"
    ------------------------------=AD------------------------------=AD---------=
    ---------------------=AD--------------------

    Sub writetabletotxtfile()
    Dim ExpRng As Range
    Dim ff As Integer
    Set ExpRng =3D Range("worksheet_to_text")
    Firstcol =3D ExpRng.Columns(1).Column
    LastCol =3D Firstcol + ExpRng.Columns.Count
    FirstRow =3D ExpRng.Rows(1).Row
    LastRow =3D FirstRow + ExpRng.Rows.Count

    ff =3D FreeFile()

    Open "C:\Documents and Settings\XYZ\Desktop\tabletote=ADxtfile.txt" For
    Output As ff
    Print #ff, ExpRng.AddressLocal()
    Print #ff, ExpRng.AddressLocal(RowAbsolut=ADe:=3DFalse,
    columnabsolute:=3DFalse)

    For r =3D FirstRow To LastRow
    For c =3D Firstcol To LastCol
    Print #ff, Cells(r, c);
    Next c
    Print #ff, 'blank line
    Next r
    Close ff
    End Sub
    ------------------------------=AD------------------------------=AD---------=
    -----------

    Here is the result txt file. see how recreated table line by line but
    still unable to get spacing right. plz help

    $B$2:$E$18
    B2:E18


    Black-Scholes Directly in a Excel Sheet


    Stock priceS 61
    Strike priceX 65
    Years to maturityT 0.25
    Risk-free rater 0.08
    Volatilityv 0.3


    d1-0.215089371482172
    d2-0.365089371482172


    European call value 2.52698589175614
    European put value 5.23989965669523
    ---------------------------------------------------------------------------=
    --------------------
    could u help me write this table retaining table spacing and
    formatting.=20

    thanks folks, deano


  8. #8
    deano
    Guest

    Re: retain table formatting when writing excel table to a txt file

    still need your help on this


  9. #9
    Tim Williams
    Guest

    Re: retain table formatting when writing excel table to a txt file

    help on what? Your original post is no longer visible, so maybe you
    could consider quoting it again....

    Tim

    "deano" <[email protected]> wrote in message
    news:[email protected]...
    > still need your help on this
    >




+ 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