help me write this table retaining table spacing and formatting
 

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

help me write this table retaining table spacing and formatting
 

Where will you be opening this file? Have you tried exporting a HTML
insted?

If you want the spacing to be maintained then you could try using a
tab-separated format.

Tim


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

help me write this table retaining table spacing and formatting
 

Thanks Tim for replying. The output file will be stored on a desktop
and can be opened in notepad. How would u modify code to retain table
spacing.....Thx,deano

help me write this table retaining table spacing and formatting
 

An alternative approach:

Copy the table to a worksheet in a new workbook.

Format the cells to have a Courier New font (nice fixed width font)
Adjust the column widths
File|SaveAs
In the "save as type" box, choose "Formatted Text (Space delimited)(*.prn)

Then open the .prn file in Notepad.

deano wrote:

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

--

Dave Peterson

help me write this table retaining table spacing and formatting
 

Dave,

is there any way to use the Print # method to wtite to a file a table
in which column spacing is not lost ?

help me write this table retaining table spacing and formatting
 

Plain text files don't have a concept of "tables" - just tabs and
spaces etc. If you want a table then you'll have to use a more
formatted approach (different file type) or pad all of your values
with spaces (even that won't work unless the app used to view the file
uses a fixed-width font)

Tim.


"deano" wrote in message
oups.com...
Dave,

is there any way to use the Print # method to wtite to a file a
table
in which column spacing is not lost ?

help me write this table retaining table spacing and formatting
 

You want a fixed width file (like for importing to a mainframe file?):

Saved from a previous post:

There's a limit of 240 characters per line when you save as .prn files.

You have a few choices (try against a copy of your worksheet):

I'd either concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/groups?threadm=015...0a% 40phx.gbl

deano wrote:

Dave,

is there any way to use the Print # method to wtite to a file a table
in which column spacing is not lost ?

--

Dave Peterson