Save Excel worksheet as comma deliminated AND quote qualifier

Posted on 2012-08-27
Last Modified: 2012-08-27
I have a worksheet with five columns that needs to be saved as a comma delimited, quote qualified file.  That is, the resulting data needs to look like the below:

"00000123456", "123.45","08082012",,"ABC21"

There is a blank column that is in column four, so that you just be commas, but how the heck do I do this in an Excel 2010 file?

Question by:ssmith94015
    LVL 39

    Accepted Solution

    You can use a macro like the one below, after selecting your data

    Sub ExportText()
    ' ExportText Macro
    Dim delimiter As String
       Dim quotes As Integer
       Dim Returned As String
      delimiter = ","
      quotes = MsgBox("Surround Cell Information with Quotes?", vbYesNo)
    ' Call the WriteFile function passing the delimiter and quotes options.
          Returned = WriteFile(delimiter, quotes)
       ' Print a message box indicating if the process was completed.
          Select Case Returned
             Case "Canceled"
                MsgBox "The export operation was canceled."
             Case "Exported"
                MsgBox "The information was exported."
          End Select
       End Sub
       Function WriteFile(delimiter As String, quotes As Integer) As String
       ' Dimension variables to be used in this function.
       Dim CurFile As String
       Dim SaveFileName
       Dim CellText As String
       Dim RowNum As Integer
       Dim ColNum As Integer
       Dim FNum As Integer
       Dim TotalRows As Double
       Dim TotalCols As Double
       ' Show Save As dialog box with the .TXT file name as the default.
       ' Test to see what kind of system this macro is being run on.
       If Left(Application.OperatingSystem, 3) = "Win" Then
          SaveFileName = Application.GetSaveAsFilename(CurFile, _
          "Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
           SaveFileName = Application.GetSaveAsFilename(CurFile, _
          "TEXT", , "Text Delimited Exporter")
       End If
       ' Check to see if Cancel was clicked.
          If SaveFileName = False Then
             WriteFile = "Canceled"
             Exit Function
          End If
       ' Obtain the next free file number.
          FNum = FreeFile()
       ' Open the selected file name for data output.
          Open SaveFileName For Output As #FNum
       ' Store the total number of rows and columns to variables.
          TotalRows = Selection.Rows.Count
          TotalCols = Selection.Columns.Count
       ' Loop through every cell, from left to right and top to bottom.
          For RowNum = 1 To TotalRows
             For ColNum = 1 To TotalCols
                With Selection.Cells(RowNum, ColNum)
                Dim ColWidth As Integer
                ColWidth = Application.RoundUp(.ColumnWidth, 0)
                ' Store the current cells contents to a variable.
                Select Case .HorizontalAlignment
                   Case xlRight
                      CellText = Space(Abs(ColWidth - Len(.Text))) & .Text
                   Case xlCenter
                      CellText = Space(Abs(ColWidth - Len(.Text)) / 2) & .Text & _
                                 Space(Abs(ColWidth - Len(.Text)) / 2)
                   Case Else
                      CellText = .Text & Space(Abs(ColWidth - Len(.Text)))
                End Select
                End With
       ' Write the contents to the file.
       ' With or without quotation marks around the cell information.
                Select Case quotes
                   Case vbYes
                      CellText = Chr(34) & CellText & Chr(34) & delimiter
                   Case vbNo
                      CellText = CellText & delimiter
                End Select
                Print #FNum, CellText;
       ' Update the status bar with the progress.
                Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
                   + ColNum) / (TotalRows * TotalCols), "0%") & " Completed."
       ' Loop to the next column.
             Next ColNum
       ' Add a linefeed character at the end of each row.
             If RowNum <> TotalRows Then Print #FNum, ""
       ' Loop to the next row.
          Next RowNum
       ' Close the .prn file.
          Close #FNum
       ' Reset the status bar.
          Application.StatusBar = False
          WriteFile = "Exported"
       End Function

    Open in new window


    Author Closing Comment

    Thomas, almost there!  Now, this pads the cells to a fixed length,  I need variable as in the example.  I am going to try to make this change myself and if I can't,then I will be back again with another question!


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    754 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

    20 Experts available now in Live!

    Get 1:1 Help Now