+ 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" <avail1now@availcompany.com> wrote in message
    news:1124891417.091288.283800@g14g2000cwa.googlegroups.com...
    > 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" <avail1now@availcompany.com> wrote in message
    news:1125720014.253598.157230@f14g2000cwb.googlegroups.com...
    > 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06620 seconds
  • Memory Usage 8,997KB
  • Queries Executed 16 (?)
More Information
Template Usage (30):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (9)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (9)postbit_legacy
  • (9)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (40):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php
  • ./includes/functions_prefix.php 

Hooks Called (44):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1