Browse All Articles > Creating and Writing to a CSV File Using Excel VBA
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/wi
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 Article2. 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.
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.
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
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!
=-=-=-=-=-=-=-=-=-=-=-=-=-
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.
Comments (4)
Author
Commented: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
Commented:
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.
Commented:
Thanks,
Rob.
Commented:
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!