Top 4 VBA Methods to Export Excel data as CSV File



Excel Macro | Excel Macro Tutorial | Excel Tips

In this article, I am going to teach you 4 VBA Methods of – how to export data from Excel Range to a CSV file format using Excel VBA.

CSV is an abbreviation of Comma Separated Value. As the name suggests, it is clear that this file stores the tabular data in a format where data is separated by comma.
Interestingly, CSV is also a plain text file type. Here each line represents a row and each value separated by comma, resides in columns.

Important Note: Since comma is used as delimiter in CSV file – so what if your data itself has comma (,) as a value? To overcome this issue, CSV file format, stores such values within double quotes (” “) and then separated by comma(,).
Let’s get started then…

Methods of Exporting Excel data to CSV Files using VBA

In this article, following are the methods which I am using to Export Excel data i CSV format.

1. Export ActiveWorkSheet as CSV file

2. VBA to export Excel Range to CSV File [Method 1]

3. VBA to export Excel Range to CSV File [Method 2]

4. VBA to Export Excel Table as CSV File

Before we go in to details, I would like to recommend you guys to go through following tutorials – this will help you in understanding the code better –
In following tutorial about interaction with text files through Excel VBA, we have talked a lot about creating new text files, exporting data from an Excel Range to Text file and so many other different topics.
VBA Guide to Interact with Text Files – Part – 1
VBA Guide to Interact with Text Files – Part – 2

1. Export ActiveWorkSheet as CSV file

Advantages of this method

1. This is a very simple and quickest method to export your Excel data to a CSV file.
2. No extra coding required in order to maintain the comma delimiter or double quotes etc. Excel does it by itself.

At the same time, this method has some short comings or challenges as well.

Drawbacks of this Method

1. In this method, data from ActiveSheet is saved as CSV file only. It ignores rest other sheets and its data.
2. You do not have control over data – which one to be exported or ignored. It will export every single data from the sheet to CSV format.
For example: If you have some blank rows at the beginning of the sheet etc., which you do not want to save it in CSV, it is not possible to ignore them. It will still save those lines as blank values in the CSV.

Best case when it should be used?

This is the best option, when your excel sheet has, the only data which you want to export it as a CSV file. That means it does not have any other data which you want to ignore while exporting it to csv.

VBA Code


Sub saveSheetToCSV()
    
    Dim myCSVFileName As String
    Dim tempWB As Workbook
    
    Application.DisplayAlerts = False
    On Error GoTo err
    
    myCSVFileName = ThisWorkbook.Path & "\" & "CSV-Exported-File-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"

    ThisWorkbook.Sheets("YourSheetToCopy").Activate
    ActiveSheet.Copy
    Set tempWB = ActiveWorkbook
    
    With tempWB
    .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
    .Close
    End With
err:
    Application.DisplayAlerts = True
End Sub

Explanation of the Code

This method is simply using the SaveAs feature of ActiveSheet to CSV format. Rest is self explanatory.

2. VBA to Export Specific Range to CSV – Method 1

Advantages of this method

This method overcomes both the challenges of the first Method.
1. Here you have full control over which all data you want to be part of your CSV file.
2. You can read data from random places and even from different sheets as well.
3. You can use your own delimiter – For example: instead of comma, you may use semicolon(;)

Drawbacks of this Method

1. The only shortcoming with this method, as compared to first method, is it has few more lines of code and execution time will be more because you are reading data for each row and column and putting them together in CSV file – separating them by comma.

Best case when it should be used?

1. When your data is scattered
2. You want to have control over data (Format check, some transformation logic etc.)

VBA Codes


Sub exportRangeToCSVFile()
    
    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim rngToSave As Range
    Dim fNum As Integer
    Dim csvVal As String
    
    Set myWB = ThisWorkbook
    myCSVFileName = myWB.Path & "\" & "CSV-Exported-File-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"
    csvVal = ""
    fNum = FreeFile
    Set rngToSave = Range("B2:H30")
    
    Open myCSVFileName For Output As #fNum
    
    For i = 1 To rngToSave.Rows.Count
        For j = 1 To rngToSave.Columns.Count
            csvVal = csvVal & Chr(34) & rngToSave(i, j).Value & Chr(34) & ","
        Next
        Print #fNum, Left(csvVal, Len(csvVal) - 2)
        csvVal = ""
    Next
    
    Close #fileNumber
End Sub

Explanation of above Code

In above code, I am doing the following:
1. It is a simple for loop, using which I am concatenating each row and columns data separated by comma (,)
2. Print each rows data in csv file.
3. That’s all… your csv file is ready to use

3. VBA to Export excel Range or Table to csv – Method 2

If you want a specific range or Table to be exported as CSV from a Worksheet, which has lot more other data as well that you want to ignore, then this method should be used. Most importantly data is huge and chances are that your data might have comma (,) or double quotes (” “) as part of values.

How this method works?

Step 1: Copy the Range or Table data in to a New WorkSheetat Cell A1
Step 2: Now this new Worksheet has “the only data” which you want to save as CSV, therefore, apply method 1 and saveAs this WorkSheet as CSV file.

Best case when it should be used?

1. When you have a clear range of data which you want to export as csv
2. Data is large enough.
3. When there are chances that your data might have comma or double quotes as a value

VBA Code


Sub saveRangeToCSV()
	
	Dim myCSVFileName As String
	Dim myWB As Workbook
	Dim tempWB As Workbook
	Dim rngToSave As Range
	
	Application.DisplayAlerts = False
	On Error GoTo err
	
	Set myWB = ThisWorkbook
	myCSVFileName = myWB.Path & "\" & "CSV-Exported-File-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"
	
	Set rngToSave = Range("C3:H50")
	rngToSave.Copy
	
	Set tempWB = Application.Workbooks.Add(1)
	With tempWB
		.Sheets(1).Range("A1").PasteSpecial xlPasteValues
		.SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
		.Close
	End With
	err:
	Application.DisplayAlerts = True
End Sub

VBA Code Explanation

Above VBA Code is doing the followings:
1. Copy the Range from your Excel Sheet – rngToSave
2. Create a new Excel Workbook
3. Paste the Copied range data in to the first sheet of the workbook from A1 cell – .Sheets(1).Range(“A1”).PasteSpecial xlPasteValues
4. SaveAs this new workbook as CSV file
5. You are done Now 🙂

4. VBA to Export excel Table to CSV format

This is the simplest method to save an excel table to CSV format. Most importantly – your data must NOT have comma (,) as part of values. In such case, you should use above method – 3.

VBA for saving Excel Table as CSV


Sub saveTableToCSV()
	
	Dim tbl As ListObject
	Dim csvFilePath As String
	Dim fNum As Integer
	Dim tblArr
	Dim rowArr
	Dim csvVal

	Set tbl = Worksheets("YourSheetName").ListObjects("YourTableName")
	csvFilePath = "C:\Users\vmishra\Desktop\CSVFile.csv"
	tblArr = tbl.DataBodyRange.Value
	
	fNum = FreeFile()
	Open csvFilePath For Output As #fNum
	For i = 1 To UBound(tblArr)
		rowArr = Application.Index(tblArr, i, 0)
		csvVal = VBA.Join(rowArr, ",")
		Print #1, csvVal
	Next
	Close #fNum
	Set tblArr = Nothing
	Set rowArr = Nothing
	Set csvVal = Nothing
End Sub

Explanation about the VBA Code above

Above code is doing the following
1. Storing the whole content of your table into a two dimensional array – tblArr
2. For each row – extract the data in to one dimensional array rowArr
3. Join all the data of single dimensional array by using comma as delimiter and store it in to a variable – csvVal
4. Print this comma separated data in the csv file (which was created)
5. Repeat this process for each row of the table – For loop is used to do so

I have tried covering all possible methods to export your excel data to CSV format.
I would really appreciate, if you provide your feedback. Do let me know by writing your comment here in the comment section of this article.

Did you like this article?

Then share it with your friends… spread knowledge…Learn All about interacting with Text Files in Excel VBA like opening, creating, writing, reading etc. from Text Files using Excel VBA code

Buy a coffee for the author

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Hurray..Lets Go

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

Feb 9, 2024

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA

Feb 3, 2024

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

Feb 3, 2024

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

Know more

LeeJay on January 9, 2018 at 4:29 pm

Expanding on Method 3, how would you copy two columns to a csv file? For example, all the data in column A and Column E.
Reply
Bruce on May 2, 2018 at 10:56 pm

thanks, I needed to convert a range of data to csv format but file needed to be named .txt not name.csv. this was surprisingly hard.
your method allowed me perform this task without save sheet as type and all that entails. this method lets me manipulate the .TXT file with fresh excel data without saving excel
Reply
- Vishwamitra Mishra on May 29, 2018 at 3:12 pm
  
  Dear Bruce,
  
  I am glad that it helped you. thanks for your feedback.
  Reply
Chris on May 10, 2018 at 8:37 pm

Using the example (2. VBA to Export Specific Range to CSV – Method 1) how can you save to a specific folder within the desktop like ChDir “C\Users\nesbie\Desktop\FolderName” or even better, just add a button on Excel that will automatically save to that folder and give you the print box so you can select a printer and print (like it would automatically save without even notifying the user?)

Thank you!

Chris
Reply
John on August 29, 2018 at 2:27 pm

I have a workbook with data on 18 rows, I want data of first 1-3 columns in row one, 4-6 in row 2, 7-9 in row 3 so on. Do we have any specific function or code to execute this.
Reply
Marco on September 27, 2018 at 7:45 am

HI!
in the method 4, how can I save the table with column headers?

thx!! 🙂
Reply
- Vishwamitra Mishra on September 27, 2018 at 9:40 am
  Hi Yes.. you can do so .. instead of using the statement
  
  tblArr = tbl.Range.Value
  
  It will do the job.
  Reply
  - eli on August 5, 2021 at 2:33 pm
    
    how can i add column headers with method 2?
    thx 🙂
    Reply
Manu Cec on October 4, 2018 at 3:03 pm

Hello,
I am reviewing this four methods. Which one would you prefer to detect empty rows (at least with column X value empty). Also, which will you prefer to save only some colums.
Many thanks
Reply
Randy Thorderson on October 4, 2018 at 5:28 pm

Awesome, thanks! I believe there is a small error at the end of (2) Direct Export Method 1…

[ Close #fileNumber ] did not close the file after writing (the file would think it was still open via Excel)

Changing it [ Close #fNum ] seem to do the trick.

Cheers!
Reply
Nikos on January 23, 2019 at 6:45 am

I am trying your very good example, but when I try to use it together with autofiltering the table is not exporting the filtered data can you help?
Set tbl = Worksheets(“FOR EXPORT”).ListObjects(“MyTable”)
‘SET AUTOFILTER
tbl.Range.AutoFilter Field:=3, Criteria1:=”*ERROR*”
Set VisRng = tbl.Range.SpecialCells(xlCellTypeVisible)
‘ copy only the visible cells in range to array
tblArr = VisRng.Value
Reply
Andrew on August 26, 2019 at 10:05 am

Hi,
I’ve used this code but my date is always exported with a hyphen rather than a forward slash. The program I am then importing this data to does not accept this format. Is there a way to format the date in the csv file with forward slashes rather than hyphens.

I’ve tried changing the format of the column in excel, that doesn’t work
Reply
- QuickUser on September 24, 2019 at 1:11 pm
  
  Does changing
  VBA.Format(VBA.Now, “dd-MMM-yyyy hh-mm”)
  to
  VBA.Format(VBA.Now, “dd/MMM/yyyy hh-mm”) work?
  Reply
Yonge on September 30, 2019 at 2:28 pm

Great. Thanks a lot.
Reply
Desmond on October 23, 2019 at 2:12 pm

Is there a way to force UTF-16 encoding?
Charset = “utf-16”
Reply
VK on November 7, 2019 at 10:03 am

I have tried Method 3, but it does not work in MAC Excel. It creates new file with the data but doesn’t save and close.
Reply
Stefan on February 16, 2020 at 9:33 am

The first one, “1. Export ActiveWorkSheet as CSV file” works perfectly but how can I adjust export to multiple CSV, based of the value of a column? Thanks in advance
Reply
pierre on May 5, 2020 at 3:55 pm

This is Great, Had 2 questions on Case 4. How can i only export rows that have values (currently its exporting even empty table rows). And for the file naming, is it possible to add a date variable for the file?

such as
csvFilePath = “C:\TEST\TEST&Date&.csv”

VBA noob here , thanks in advance 🙂
Reply
Brian on November 26, 2020 at 7:17 pm

Hi,

Thanks very much for this tutorial, helped me a lot.

I tried listing 2 and found that it had 2 bugs:

1. Print #fNum, Left(csvVal, Len(csvVal) – 2) should be Print #fNum, Left(csvVal, Len(csvVal) – 1)
2. Close #fileNumber should be Close #fNum

Cheers!

Brian
Reply
Rodger D Benavides on December 11, 2020 at 4:51 pm

Thank you for this! I was looking for a simple way to save a worksheet as a CSV. I modified the code to suite my needs, and save to a specific folder.
Reply
stephen on March 30, 2021 at 9:18 am

Hello, Thanks for this i do have a question regarding version 4 on the line

rowArr = Application.Index(tblArr, i, 0) i get a runtime error 13 type mismatch

does anyone have any ideas
Reply
Johnson O on April 7, 2021 at 1:06 pm

Thank you so much for the effort in making this, I was looking for a simple option. I have adapted your code into my project
Reply
LALITA on July 17, 2021 at 12:08 pm

HOW TO SAVE DATA IN .CSV TEMPLET
Reply
Devesh Gupta on September 26, 2021 at 2:50 pm

This Code is not working. I am trying to save active excel sheet every min in csv format. first time it works from 2nd time i get error this macro can run. please check. thanks in advance

Public RunWhen As Double
Sub saveSheetToCSV()

Dim myCSVFileName As String
Dim tempWB As Workbook

Application.DisplayAlerts = False
On Error GoTo err

myCSVFileName = ThisWorkbook.Path & “\” & “CSV-Exported-File-” & VBA.Format(VBA.Now, “dd-MMM-yyyy hh-mm”) & “.csv”

ThisWorkbook.Sheets(“WorkArea”).Activate
ActiveSheet.Copy
Set tempWB = ActiveWorkbook

With tempWB
.SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
err:
Application.DisplayAlerts = True
End Sub

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 1, 0)
Application.OnTime earliesttime:=RunWhen, procedure:=”saveSheetToCSV”, _
schedule:=True
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=”saveSheetToCSV”, schedule:=False
End Sub
Private Sub Workbook_Open()
Call saveSheetToCSV
Call StartTimer
End Sub
Reply
Mario L Heureux on October 2, 2021 at 8:09 pm

Bonjour, there is a mistake in Method 1

Open myCSVFileName For Output As #fNum

For i = 1 To rngToSave.Rows.Count
For j = 1 To rngToSave.Columns.Count
csvVal = csvVal & Chr(34) & rngToSave(i, j).Value & Chr(34) & “,”
Next
Print #fNum, Left(csvVal, Len(csvVal) – 2)
csvVal = “”
Next

Close #fileNumber

–> The close file should be “Close #fileNumber” in lieu of “Close #fileNumber”

Thank you, this code was helpful
Reply
Gerd Petzold on November 6, 2023 at 7:06 pm

Great material to compare and to start with. Thanks a lot!
Have chosen and modified the second chapter because of the flexibility.
One comment: Putting the “Print” behind the loop makes it much quicker.
Reply