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 = Range("worksheet_to_text")
'Set ExpRng = ActiveCell.CurrentRegion
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\tabletote*xtfile.txt" For
Output As ff
Print #ff, ExpRng.AddressLocal()
Print #ff, ExpRng.AddressLocal(RowAbsolut*e:=False,
columnabsolute:=False)


For r = FirstRow To LastRow
For c = FirstCol To LastCol
vdata = 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.


thanks folks, deano

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

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

retain table formatting when writing excel table to a txt file
 

plz folks, still looking for your help

retain table formatting when writing excel table to a txt file
 

plz folks, still looking for your help

retain table formatting when writing excel table to a txt file
 

"deano" wrote in message
oups.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

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"
------------------------------*------------------------------*------------------------------*--------------------

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\tabletote*xtfile.txt" For
Output As ff
Print #ff, ExpRng.AddressLocal()
Print #ff, ExpRng.AddressLocal(RowAbsolut*e:=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
-----------------------------------------------------------------------------------------------
could u help me write this table retaining table spacing and
formatting.

thanks folks, deano

retain table formatting when writing excel table to a txt file
 

still need your help on this

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" wrote in message
oups.com...
still need your help on this