MrExcel Publishing
Your One Stop for Excel Tips & Solutions

text file format

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

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

' 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
' defines the file location and name
Open "C:\My Documents\newfile.txt" For Output As #1
' Writes the file
Print #1, TxtToWrite
close #1