Community Pick: Many members of our community have endorsed this article.

Creating and Writing to a CSV File Using Excel VBA

Dave
CERTIFIED EXPERT
Published:

Introduction

This Article provides two VBA code samples to create and write to a CSV file:

1) Creating a CSV file using the Open For Output as FreeFile.
2) Creating a CSV file using the FileSystemObject object.

I prefer the latter approach mainly as I am using the FileSystemObject for further coding, for example processing all files in subfolders recursively (though that technique is not used in this article).

 

Creating CSV files

What's the point?
As per http://en.wikipedia.org/wiki/Comma-separated_values CSV is a simple file format that is widely supported, so it is often used to move tabular data between different computer programs that support the format.

For the VBA coder:
 
Writing to a CSV file is an efficient method to produce a report file, especially when this technique is coupled with Variant Arrays.

CSV files do not have the row limit that Excel is subject to.

The report writing is simple, each row is appended to the CSV file (using writeline or Print #), so there is no need to determine the endpoint of the report as is needed when appending VBA ranges to existing ranges.
The two code samples below use Variant Arrays to create a transposed output (i.e. transposing columns for rows) of every WorkSheet in the ActiveWorkbook. Readers may find my Article on using Variant Arrays rather than loops useful,  Using Variant Arrays in Excel VBA for Large Scale Data Manipulation.

 

Understanding the Code

There are 5 major portions in the sample code:
 
The UsedRange of each sheet is tested. If there is no UsedRange (i.e. an empty WorkSheet) then the code skips that particular WorkSheet. If there is only a single cell then the Variant Array approach is not utilised as this approach needs a minimum of two cells to satisfy the 2D array requirement. The two IF tests are run separately to avoid test for a single cell if the sheet is already known to be blank.

The Variant Array is processed, looping through each column, row by row.

As the first value in strTmp should not be preceded by a delimiter it is written as value only. Note that this is more efficient than testing for the first value inside the For …. Each loop.

Note that I have escaped any comma (",") values in the cells with "" as per the standard Excel treatment for CSV files.

The entire column is written to the CSV file using either Print #lFnum, strTmp or objTF.writeline strTmp, depending on which of the functions is used.
 

Using the Code

  1. Copy the code at the bottom of this Article
   2. Open any workbook.
   3. Press Alt + F11 to open the Visual Basic Editor (VBE).
   4. From the Menu, choose Insert-Module.
   5. Paste the code into the right-hand code window.
   6. Close the VBE, save the file if desired.
 
In xl2003 go to Tools-Macro-Macros and double-click  CreateCSV_Output or CreateCSV_FSO
In xl2007 click the Macros button in the Code group of the Developer tab, then click CreateCSV_Output or CreateCSV_FSO in the list box.

Notes:


This code must be run from a regular VBA Code Module.  Otherwise the code will cause an error if users try to run it from the ThisWorkbook or Sheet Code panes given the usage of Const.
 

It is worth noting that the ThisWorkbook and Sheet code sections should be reserved for Event coding only, "normal" VBA should be run from standard Code Modules.

Please note that for purposes of the sample code, the file path of the CSV output file is "hard-coded" as:
      C:\test\myfile.csv
at the top of the code.  You will probably want to set the output file programmatically, for instance as a function parameter.

As mentioned earlier;   For example purposes, this code TRANSPOSES COLUMNS AND ROWS; that is, the output file contains one CSV row for each column in the selected range.  Normally, CSV output would be row-by-row, echoing the layout visible on screen, but I wanted to demonstrate that generating the output by using VBA code provides options beyond what is available by, for instance, using the Save As... CSV Text menu option.

Const sFilePath = "C:\test\myfile.csv"
                      Const strDelim = ","
                      Sub CreateCSV_Output()
                          Dim ws As Worksheet
                          Dim rng1 As Range
                          Dim X
                          Dim lRow As Long
                          Dim lCol As Long
                          Dim strTmp As String
                          Dim lFnum As Long
                      
                          lFnum = FreeFile
                          Open sFilePath For Output As lFnum
                      
                          For Each ws In ActiveWorkbook.Worksheets
                              'test that sheet has been used
                              Set rng1 = ws.UsedRange
                              If Not rng1 Is Nothing Then
                                  'only multi-cell ranges can be written to a 2D array
                                  If rng1.Cells.Count > 1 Then
                                      X = ws.UsedRange.Value2
                                      'The code TRANSPOSES COLUMNS AND ROWS by writing strings column by column
                                      For lCol = 1 To UBound(X, 2)
                                          'write initial value outside the loop
                                           strTmp = IIf(InStr(X(1, lCol), strDelim) > 0, """" & X(1, lCol) & """", X(1, lCol))
                                          For lRow = 2 To UBound(X, 1)
                                              'concatenate long string & (short string with short string)
                                              strTmp = strTmp & (strDelim & IIf(InStr(X(lRow, lCol), strDelim) > 0, """" & X(lRow, lCol) & """", X(lRow, lCol)))
                                          Next lRow
                                          'write each line to CSV
                                          Print #lFnum, strTmp
                                      Next lCol
                                  Else
                                      Print #lFnum, IIf(InStr(ws.UsedRange.Value, strDelim) > 0, """" & ws.UsedRange.Value & """", ws.UsedRange.Value)
                                  End If
                              End If
                          Next ws
                      
                          Close lFnum
                          MsgBox "Done!", vbOKOnly
                      
                      End Sub
                      
                      Sub CreateCSV_FSO()
                          Dim objFSO
                          Dim objTF
                          Dim ws As Worksheet
                          Dim lRow As Long
                          Dim lCol As Long
                          Dim strTmp As String
                          Dim lFnum As Long
                      
                          Set objFSO = CreateObject("scripting.filesystemobject")
                          Set objTF = objFSO.createtextfile(sFilePath, True, False)
                      
                          For Each ws In ActiveWorkbook.Worksheets
                              'test that sheet has been used
                              Set rng1 = ws.UsedRange
                              If Not rng1 Is Nothing Then
                                  'only multi-cell ranges can be written to a 2D array
                                  If rng1.Cells.Count > 1 Then
                                      X = ws.UsedRange.Value2
                                      'The code TRANSPOSES COLUMNS AND ROWS by writing strings column by column
                                      For lCol = 1 To UBound(X, 2)
                                          'write initial value outside the loop
                                          strTmp = IIf(InStr(X(1, lCol), strDelim) > 0, """" & X(1, lCol) & """", X(1, lCol))
                                          For lRow = 2 To UBound(X, 1)
                                              'concatenate long string & (short string with short string)
                                              strTmp = strTmp & (strDelim & IIf(InStr(X(lRow, lCol), strDelim) > 0, """" & X(lRow, lCol) & """", X(lRow, lCol)))
                                          Next lRow
                                          'write each line to CSV
                                          objTF.writeline strTmp
                                      Next lCol
                                  Else
                                      objTF.writeline IIf(InStr(ws.UsedRange.Value, strDelim) > 0, """" & ws.UsedRange.Value & """", ws.UsedRange.Value)
                                  End If
                              End If
                          Next ws
                      
                          objTF.Close
                          Set objFSO = Nothing
                          MsgBox "Done!", vbOKOnly
                      
                      End Sub

Open in new window

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author, please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=    
13
45,582 Views
Dave
CERTIFIED EXPERT

Comments (4)

CERTIFIED EXPERT

Author

Commented:
No probs :)

For an exact export it would be more efficient to use my Collating Worksheets article then a SaveAs CSV as opposed to a write

The transpose was just a means to an end - I needed a sample for dumping to a CSV. And I figured someone - more so an experienced user - may take some benefit out of an array write

Cheers

Dave
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Dan, I think it's fine.  Thanks Dave for your patience.  One last thing, I would change
I prefer the later approach

to
I prefer the latter approach

but that's my English (Australian)...I'm not sure if you guys are the same?

Other than that, I can't see anything wrong with it.

Regards,

Rob,
EE Page Editor.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Dave, nice article!  Makes transposing output very easy, while still keeping the Excel data intact....very nice!

Thanks,

Rob.

Commented:
Hi brettdj,
thanks for the code.
one question though.. how can i modify the code  so i will get a CSV file exactly like the original sheet. i mean without changing the columns and rows.
basicly all i need is to save/export a xlsm file to a CSV (WINDOWS) without any changes to the data, of course the active sheet will be exported.
i liked the msg box you added and of course the fact that the new CSV file isnt opening automaticly.
Thanks in advance!

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.