[Last Call] Learn how to a build a cloud-first strategyRegister Now


Save an Excel 2010 worksheet as a comma deliminted text file

Posted on 2012-08-27
Medium Priority
Last Modified: 2012-08-27
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.

Question by:ssmith94015
LVL 12

Assisted Solution

Seaton007 earned 600 total points
ID: 38338611
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
LVL 50

Assisted Solution

by:Martin Liss
Martin Liss earned 700 total points
ID: 38338632
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

LVL 24

Accepted Solution

Steve earned 700 total points
ID: 38338655
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


Author Closing Comment

ID: 38338672
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.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question