Converting Excel data to .txt

alanf

New Member
Joined
Mar 26, 2002
Messages
6

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Does the file have to be .txt? Could it have another extension like ".csv"? If it can then stick your data in columns A B and C then go to File|Save As and change the file type to Comma Separated Values (*.csv).
 
Upvote 0
Yep. Unfortunately it has to be .txt format. You would think there would be a way to select export and then tell Excell what kind of seperators you want. I can't figure it out though.

Alan
 
Upvote 0
Thanks HTH.

That is exactly what I need to do. Any idea of how to do the additional formatting? I don't necessarily have to add the [ ] around the headers. I can do that manually. It is going to be a pain to manually add " " around each item in the list...got any ideas?

Maybe i could user some formula or something to add the quotes around each item then convert it to .csv and then rename it to .txt? Anyone know a way to add " " around the text in each cell in an Excell document?

Alan
This message was edited by alanf on 2002-03-27 14:55
 
Upvote 0
Hi,

You might want to try the following:

Open "C:FileName.txt" For Output As #1
For i = 1 To 10
Print #1, """" & Range("A1").Offset(i - 1, 0).Value & """" & "," & _
"""" & Range("A1").Offset(i - 1, 1).Value & """" & "," & _
"""" & Range("A1").Offset(i - 1, 2).Value & """"
Next i
Close #1

The above code is assuming your column A is the first name, column B is surname, and column C is the E-mail Address.

HTH

PS. Yogi is too quick for me. But saving as .csv, and rename as .txt, the data in the file don't have the quotation marks.
This message was edited by BabyTiger on 2002-03-27 14:57
 
Upvote 0
Hi,

From the workbook/sheet that's got the list of name and e-mail address, select from menu bar, tools-macro-visual basic editor, and then from the microsoft visual basic screen, select from menu bar, insert module.

And then copy those codes to that module with an addition line before the code, "Sub Test()", and after code, enter "End Sub" in the line after.

Once you have done all that, you have setup your macro, and is ready to go.

Now make sure your data is in the right place. ie. First names are in the column A, Surnames are in column B, and e-mail addresses are in column C.

The codes in the previous post will not insert the headers, if your want to add that, then use the codes below:

Open "C:FileName.txt" For Output As #1
Print #1, "[firstname],[lastname],"
For i = 1 To 10
Print #1, """" & Range("A1").Offset(i - 1, 0).Value & """" & "," & _
"""" & Range("A1").Offset(i - 1, 1).Value & """" & "," & _
"""" & Range("A1").Offset(i - 1, 2).Value & """"
Next i
Close #1

The difference is that extra line of code, that will add the headers to the txt file.

HTH
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top