<

Creating and Writing to a CSV File Using Excel VBA

Published on
32,009 Points
23,209 Views
13 Endorsements
Last Modified:
Approved
Community Pick

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
Comment
Author:Dave
  • 2
4 Comments
 
LVL 50

Author Comment

by:Dave
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
0
 
LVL 65

Expert Comment

by:RobSampson
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.
0
 
LVL 65

Expert Comment

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

Thanks,

Rob.
0
 

Expert Comment

by:Yairda
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!
0

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month