none
Saving Excel file as text file with pipe (|) as delimiter

    Question

  • I have an excel 2007 workbook and want to save it using a macro.  The resultant file will be a pipe delimited text file.

    I have changed the delimiter in the regional settings in control panel.  If I save the file manually using file save as csv I get the correct format.  If I use a recorded macro to save the file it has commas instead of pipes (|).

    Here is the macro:

    Sub WorldDoc()
    '
    ' WorldDoc Macro
    ' Save document with pipe (|) as delimiter.
    '
    ' Keyboard Shortcut: Ctrl+w
    '
        ChDir "c:\Transfers"
        ActiveWorkbook.SaveAs Filename:= _
            Range("C1").Text, _
            FileFormat:=xlCSV, CreateBackup:=False
    End Sub

    Any ideas?


    Thanks, Jim
    Tuesday, March 23, 2010 3:39 PM

Answers

  • I don't think it's possible. SaveAs uses the commas as separators or what is specified in the local settings.

    Another approach is to create the csv file using the "Open", "Line Input" and "Close" instructions.

    You are then able to use the separator of your choice.

    HTH

    Daniel

    • Marked as answer by Jim Welborne Thursday, March 25, 2010 2:35 PM
    Wednesday, March 24, 2010 8:58 PM

All replies

  • Use the "local" parameter:

    Local=true

    in the SaveAs method.

    Regards.

    Daniel

    • Marked as answer by Jim Welborne Wednesday, March 24, 2010 2:54 PM
    • Unmarked as answer by Jim Welborne Wednesday, March 24, 2010 7:30 PM
    Wednesday, March 24, 2010 11:00 AM
  • OK that worked.

    Another question - Is there a way to have the SaveAs use the pipe as the delimeter so we would not have to use control panel to modify the delimeter?

     


    Thanks, Jim
    • Proposed as answer by Jovi Salonga Wednesday, May 04, 2011 5:31 AM
    • Unproposed as answer by Jovi Salonga Wednesday, May 04, 2011 5:31 AM
    Wednesday, March 24, 2010 2:56 PM
  • I don't think it's possible. SaveAs uses the commas as separators or what is specified in the local settings.

    Another approach is to create the csv file using the "Open", "Line Input" and "Close" instructions.

    You are then able to use the separator of your choice.

    HTH

    Daniel

    • Marked as answer by Jim Welborne Thursday, March 25, 2010 2:35 PM
    Wednesday, March 24, 2010 8:58 PM