Excel VBA: To CSV Or Not To CSV

Photo by Mika Baumeister on Unsplash

The comma-separated values (CSV) file predates the personal computer and was first used by IBM Fortran in 1972!

If you use Excel… at all… you have likely interacted with hundreds of CSV files. In fact, CSV is a native Excel file format. Double-clicking on one opens the file in Excel on most machines.

But the CSV file format is both drop-dead simple and annoying at the same time. Just about every computing environment supports CSV, but there is no agreed upon standard to handle the details. What to do with commas within a value? Or with quotation marks within a value? Or line breaks?

Prior to November of 2016, no version of Excel could save a CSV file without slaughtering UTF-8 Unicode characters. Really? Yes, really.

I’m still on Excel 2013. What to do?

Well, it would be nice to be able to save the current worksheet to a CSV file without losing focus of the current worksheet.

Here’s a short VBA routine to do just that… AND it honors UTF-8.

Sub SaveSheetAsCSV()
  Dim i&, j&, iMax&, jMax&, p&, listsep$, s$, t$, v
  Const q = """", qq = q & q
  listsep = Application.International(xlListSeparator)
  With ActiveSheet
    v = .UsedRange.Value
    iMax = UBound(v, 1): jMax = UBound(v, 2)
    s = Space("5e6")
    For i = 1 To iMax
     For j = 1 To jMax
      If Not IsError(v(i, j)) Then
        t = v(i, j)
      Else
        t = .Cells(i, j).Text
      End If
      If InStr(t, q) Or InStr(t, listsep) Or InStr(t, vbLf) Then
        t = Replace(t, q, qq): t = q & t & q
      End If
      t = t & listsep
      Mid(s, p + 1, Len(s)) = t
      p = p + Len(t)
     Next
     If i < iMax Then Mid(s, p, 2) = vbCrLf: p = p + 1
    Next
    t = Left(.Parent.Name, InStrRev(.Parent.Name, ".")) & _
        .Name & ".csv"
    SaveStringAsTextFile Left(s, p), t
  End With
End Sub
Function SaveStringAsTextFile$(s$, fName$)
    Const adSaveCreateOverWrite = 2
    With CreateObject("ADODB.Stream")
        .Charset = "utf-8"
        .Open
        .WriteText s
        .SaveToFile fName, adSaveCreateOverWrite
    End With
End Function

This routine is crazy fast. It builds the CSV file from scratch forgoing the tired ActiveWorkbook.SaveAs hacks.

My routine aims to be RFC 4180 compliant. It manages line-breaks, commas, and quotes within values.

It handles error values, currency values, and dates.

It also utilizes the system’s local list separator, which may be something other than a comma. For example, it might be a semicolon or something else.

It saves the new CSV file in the same folder as the active workbook. It names the file for you, overwriting existing files with the exact same name, and follows this naming scheme: book1.sheet1.csv

Add the routine to your Personal workbook or Add-in and configure a keyboard shortcut. Then save your worksheets with no fuss or flicker, and so fast you’ll grin.

Examine this routine, line by line. I might be able to teach you a thing or two.

Let me know your thoughts in the comments.