Save an Excel 2010 worksheet as a comma deliminted text file

I have a worksheet that I need saved as comma deliminated, but have no idea how to get this into a txt from the worksheet.

Sandra SmithRetiredAsked:
Who is Participating?
here is a little bit of code which creates the same text file for the activesheet:
very similar to above.
Sub test()
Dim wb As Workbook
Dim NewWb As String
Dim ws As Worksheet
Set wb = ActiveWorkbook
NewWb = "C:\Users\xxx\Documents\Book6.txt"
Application.DisplayAlerts = False
wb.SaveAs Filename:=NewWb, FileFormat:=xlCSV, CreateBackup:=False
wb.Close (False)
Application.DisplayAlerts = True
End Sub

Open in new window

1. Choose Save As
2. Change the Save as type to CSV (Comma Delimited)
3. Give it a File name
4. Click Save
5. Go to the location where you saved the file, right-click on it and choose Rename
6. Change the extension from .csv to .txt
Martin LissOlder than dirtCommented:
Or if you're going to be doing it more than once, record a macro when you do it the first time (the result will be something like the following) and assign it a shortcut letter. You would of course have (or change to) a different path and file name. If you wanted to you could modify the macro to ask for the file name and/or path. Using the macro from then on would only take the typing of a ctrl-z (in this case).

Sub Macro1()
' Macro1 Macro
' Keyboard Shortcut: Ctrl+z
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Martin Liss\My Documents\Book1.csv", FileFormat:= _
        xlCSV, CreateBackup:=False
End Sub

Open in new window

Sandra SmithRetiredAuthor Commented:
Actually, it turns out to be even more complex as the file not only has to have comman deliminaters, each column must have quotes as the text qualifier!  I simply do not know how the previously person did it as I cannot find anything.  I am going to post another question for this part as I did get it to save with the comma, but now I need the quotes! Am splitting as they all actually did what I asked.  Slightly more for the code as I have to do this in VBA.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.