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

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

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

plz folks, still looking for your help

plz folks, still looking for your help

"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

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

still need your help on this

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
>