MrExcel Publishing
Your One Stop for Excel Tips & Solutions

text file format


Posted by kim on October 16, 2001 2:28 AM

hi,
can u tell me is there any way to change the text file, which i previously save as text(tab delimited) in MsExel format, to another form.However, this file, when i first open, i opened as fixed width. Where, my text will be in columns
The another form i refer to is to choose delimited (tab)when i open it, where all the text will be in one column only.
Suppose, i directly open my file by choosing delimited(tab), i'm able to do that. but, when i saved it as tab delimited text after opening as fixed width,i'm not able to do that.
Can u pls help me out


Posted by Jason Davis on October 23, 2001 3:50 AM

If excel will not "understand" the format you really want, open the file in Windows Note pad,
save it as just a text document (new name.txt), then open it in Excel. Another thing you might do is from Notepad, copy then paste it
into excel. If all of that doesnt work, you might want to write it to a text file only from Excel VBA.

Put the following code into a macro then run it. It will create a text only file in My Documents called newfile.txt Just copy and paste it
into a macro.

Good luck
Jason

' Defines Varibles
'
Dim MyRg as Range
Dim TextCell as Range
Dim TxtToWrite as String
'
' this defines the range to write as a text file
'
Set MyRg = Sheets("sheet1").Range("A1:A20")
For Each TextCell in MyRg
'
' formats for text only
'
TxtToWrite = TxtToWrite & TextCell.Text & vbCrlf
Next
'
' defines the file location and name
'
Open "C:\My Documents\newfile.txt" For Output As #1
'
' Writes the file
'
Print #1, TxtToWrite
'
close #1