Adding a comma and space

[deano6410]

Hello everyone.

I have been struggling with a small problem and I am hoping you can help me with it. (Thanks in advance)

Basically I have a list of emails in a horizontal column and I wish to export them to a .txt or .doc document but with a comma and a space between each one.

e.g email, email2, email3, email4 etc...

If someone can please tell me the best way to do this I would be very greatful.

p,s I am on Excel 2003

Thanks

 

[deano6410]

if anyone thinks this CAN'T be done then that is fine. I am just keen to give an answer to my partner.

Thanks

 

[deano6410]

Anyone?

This is very important to my partner, so any help would be superb.

 

[Scott Huish]

You said horizontal column ?? Are they in a column or row? Are they just in 1 row or column? What row or column?

 

[Joe Was]

What do you mean by: horizontal column.
A horizontal column = a ROW!

In any case this code should do it?
You need to open the NotePad and create a blank .txt file to work with, save it to the same folder as your WorkSheet.


Sub addToTextFile()
'Standard Module Code, Like: Module1.
'Will append file if needed!

'ForReading 1 Open a file for reading only. You can't write to this file.
'ForAppending 8 Open a file and write to the end of the file.

'TristateUseDefault –2 Opens the file using the system default.
'TristateTrue –1 Opens the file as Unicode.
'TristateFalse 0 Opens the file as ASCII.

Const ForReading = 1, ForWriting = 2, ForAppending = 3
'Dim fs, f
Dim myFile As String
Dim NPOPFile As String
Dim myText As String
Dim MessageT, TitleT, DefaultT
Dim Msg, Style, Title, Help, Ctxt, Response
Dim myRange As Range

Msg = "You will append your new text to any in the file you select if you continue!" _
& vbCr & vbCr & "Do you want to continue?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Caution!" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
GoTo myContinue
Else ' User chose No.
GoTo myEnd
End If

'Get text to write to file!
myContinue:
'Ask user to select range for text file.
Set myRange = Application.InputBox(prompt:="Please select a range!", _
Title:="Text File Range!", Type:=8)

For Each Cell In myRange
myText = myText & Cell.Value & ", "
Next Cell

On Error GoTo Err_OpenTextFile
'Use this Text file!
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
End If

myFile = fileToOpen
NPOFile = "NotePad.exe " & myFile

'Work with file [Append Text to File].
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(myFile, 8, TristateUseDefault)
f.Write Chr(9) & myText
f.Close

'Open NotePad with a data file!
ActiveSheet.Select
Call Shell(NPOFile, 1)

Exit_OpenTextFile:
Exit Sub

Err_OpenTextFile:
MsgBox Err.Description
Resume Exit_OpenTextFile
myEnd:
End Sub

 

[deano6410]

sorry guys.

They are VERTICAL.

Apologies for the mix up.

 

[Joe Was]

My code above should work for you. You did not say if you will be replacing the file each time you run it or if you will be adding new data to the end of the old data in the text file. In any case the code above will work. Just create a blank text file using NotPad from Windows once you have a named .txt file. Run the code from the Workbook that has your data. it will ask you for the data range, you select it with your mouse, it then asks you to select the text file to use. It is for the most part an automatic application.