Save an Excel 2010 worksheet as a comma deliminted text file

Posted on 2012-08-27
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

    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 44

    Assisted Solution

    by:Martin Liss
    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now