VBA – Export Range to CSV (Delimited Text File)

Save Range to CSV File

Solution #670 on how to export a range to a delimited text file.

This is a handy alternative to using Excel’s default way to save the worksheet as a Text file or CSV delimited file by choosing “save as” when:

1. You use a template and only want to export the data excluding headers and other misc stuff

2. Similarly, You may only want to export part of a dataset

3. You want to save the file with a custom delimiter that may be unique to your application.

If you have a need to do 1,2, or 3 a function like the following may help. It accepts a range to export, location to save the file, and the delimiter you’d like your data separated with, then saves your data as specified.

Here’s how to call the function ExportRange:

Sub CallExport()

  'ExportRange(range,where,delimiter)
  Call ExportRange(Sheet1.Range("A1:C20"), _

  "C:mark.txt", ",")

End Sub

First you tell the function the range you want to export, then where to export it, than the delimeter to use. You’ll also need the function ExportRange, here it is:

Function ExportRange(WhatRange As Range, _
         Where As String, Delimiter As String) As String

  Dim HoldRow As Long    'test for new row variable
  HoldRow = WhatRange.Row

  Dim c As Range

  'loop through range variable
  For Each c In WhatRange
    If HoldRow <> c.Row Then
      'add linebreak and remove extra delimeter
      ExportRange = Left(ExportRange, Len(ExportRange) - 1) _
                          & vbCrLf & c.Text & Delimiter
        HoldRow = c.Row
    Else
        ExportRange = ExportRange & c.Text & Delimiter
    End If
Next c

'Trim extra delimiter
ExportRange = Left(ExportRange, Len(ExportRange) - 1)

'Kill the file if it already exists
If Len(Dir(Where)) > 0 Then
    Kill Where
End If

Open Where For Append As #1    'write the new file
Print #1, ExportRange
Close #1
End Function