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
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!