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


------------------------------=AD------------------------------=AD-------

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-=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=AD=ADxtfile.txt"
For
Output As ff
Print #ff, ExpRng.AddressLocal()
Print #ff, ExpRng.AddressLocal(RowAbsolut=AD=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-----------------------------=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
------------------------------=AD------------------------------=AD---------=
---------------------=AD-----

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


thanks folks, deano

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" <avail1now@availcompany.com> wrote in message
news:1125720140.919889.24330@g49g2000cwa.googlegroups.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

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

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

Dave,

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

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" <avail1now@availcompany.com> wrote in message
news:1125941671.887475.193140@z14g2000cwz.googlegroups.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 ?
>

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...280a%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