Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
retain table formatting when writing excel table to a txt file
plz folks, still looking for your help
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
retain table formatting when writing excel table to a txt file
plz folks, still looking for your help
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
retain table formatting when writing excel table to a txt file
still need your help on this
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When refreshed, some Pivot Table columns retain formatting, some d | Excel Worksheet Functions | |||
Retain pivot table formatting after refreshing data | Excel Worksheet Functions | |||
2007 Excel Table and Pivot Table Formatting | Excel Discussion (Misc queries) | |||
How can I retain the formatting of the data in a lookup table? | Excel Discussion (Misc queries) | |||
How to retain table column titles in 2007 | Excel Discussion (Misc queries) |