Export Range to CSV (Delimited Text File)

VBA – Export Range to CSV (Delimited Text File)

Save Range to CSV File

export range deliminated 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!