Adding a comma and space

deano6410

New Member
Joined
Sep 21, 2006
Messages
4
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

deano6410

New Member
Joined
Sep 21, 2006
Messages
4
if anyone thinks this CAN'T be done then that is fine. I am just keen to give an answer to my partner.

Thanks
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

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
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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.
 

Forum statistics

Threads
1,141,628
Messages
5,707,503
Members
421,511
Latest member
mgroah1

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
Top