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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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!
