Converting Excel data to .txt

[alanf]

I need to convert some data I have in .xls format into .txt format. I also have to format it a certain way for a mail program that I have.

Here is how the data needs to be formatted:

[firstname],[lastname],[email]
"Alan","Smith","alan@aol.com"
"Tom","Jones","tomj@aol.com"
etc.

Does anyone know how to export this data and format it this way automatically?

Thanks,

Alan

[ This Message was edited by: alanf on 2002-03-27 14:49 ]

[Mark O'Brien]

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).

[alanf]

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

[Yogi Anand]

On 2002-03-27 14:25, alanf wrote:
I need to convert some data I have in .xls format into .txt format. I also have to format it a certain way for a new mail program that I have.

Here is how the data needs to be formatted:

[firstname],[lastname],[email]
"Alan","Smith","alan@aol.com"
"Tom","Jones","tomj@aol.com"
etc.

Does anyone know how to export this data and format it this way automatically?

Thanks,

Alan

Hi alanf:
Continuing with Mark's suggestion with saving data in your Excel worksheet as a CSV (comma delimited) format ...
after you have saved this file with the CSV extension, you can rename it with TXT extension.
I took your sample data, saved the data in CSV format and opened it in Notepad ... here is what I got:

first,last,email
Alan,Smith,alan@aol.com
Tom,Jones,tomj@aol.com

If you need to enclose the field headers in [] and you need to have each piece of data in double quotes, you may have some additional formatting to do.

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

[alanf]

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 ]

[BabyTiger]

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 ]

[alanf]

What do I do with the code? Sorry I am a novice...don't know what to do with it.

[BabyTiger]

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],[email]"
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

[Yogi Anand]

Hi BabyTiger:
I tried the code in a sub -- Works ... Beautiful!