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

<<Return to VBA Examples