12

I need to output some results as a .csv file, that gets parsed later on by another process. In order to produce these results, I have a huge workbook containing all the macros and functions that I need.

  1. Is it possible to "create" a separate .csv file from VBA?
  2. Is it possible to use VBA features to write into it instead of just writing in a "raw textual" approach?
TylerH
  • 20,799
  • 66
  • 75
  • 101
BuZz
  • 16,318
  • 31
  • 86
  • 141
  • 1
    Without more details it will be difficult to assist you. What is the source of the data and why do you need to create a CSV from scratch instead of using Excel's native method (SaveAs)? – JimmyPena Dec 07 '11 at 18:08
  • Does this answer your question? [Excel: macro to export worksheet as CSV file without leaving my current Excel sheet](https://stackoverflow.com/questions/37037934/excel-macro-to-export-worksheet-as-csv-file-without-leaving-my-current-excel-sh) – neves Jul 29 '20 at 23:44

5 Answers5

28

Is something like this what you want?

Option Explicit
Sub WriteFile()

  Dim ColNum As Integer
  Dim Line As String
  Dim LineValues() As Variant
  Dim OutputFileNum As Integer
  Dim PathName As String
  Dim RowNum As Integer
  Dim SheetValues() As Variant

  PathName = Application.ActiveWorkbook.Path
  OutputFileNum = FreeFile

  Open PathName & "\Test.csv" For Output Lock Write As #OutputFileNum

  Print #OutputFileNum, "Field1" & "," & "Field2"

  SheetValues = Sheets("Sheet1").Range("A1:H9").Value
  ReDim LineValues(1 To 8)

  For RowNum = 1 To 9
    For ColNum = 1 To 8
      LineValues(ColNum) = SheetValues(RowNum, ColNum)
    Next
    Line = Join(LineValues, ",")
    Print #OutputFileNum, Line
  Next

  Close OutputFileNum

End Sub

Don't forget you will need to put quotes around any field containing a comma.

Tony Dallimore
  • 12,335
  • 7
  • 32
  • 61
  • Don't know why, but if I modify of comment any of this code and run it, the SheetValues get merged into one or two columns and reverting the code to the one in this post don't fix the issue. Just spent a whole day trying to test this stuff and couldn't find the reason. – Alfabravo Mar 26 '13 at 22:45
  • @ Alfabravo. If you go to my profile you will find an email address. If you send me a copy of your faulty code I will have a look. – Tony Dallimore Mar 27 '13 at 23:49
  • @tonyDallimore, what does the "Lock Write as #OutputFileNum" do? – Austin Wismer Apr 28 '15 at 15:13
  • @Austin Wismer I know of no changes to the VBA Open statement since Office 2003. It may be unchanged from earlier versions but my knowledge does not go back before 2003. "Lock Write" means no other program can write to this file until this macro has closed it. See https://msdn.microsoft.com/en-us/library/office/gg264163.aspx for the specification of the Office 2013 Open statement. – Tony Dallimore Apr 28 '15 at 16:59
  • Thanks! What is the difference between using the PRINT and WRITE commands? – Austin Wismer Apr 28 '15 at 20:04
  • See [Print # statement](https://msdn.microsoft.com/en-us/library/office/gg264278.aspx) and [Write # statement](https://msdn.microsoft.com/en-us/library/office/gg264524.aspx). – Tony Dallimore Apr 28 '15 at 22:48
  • Beware with newlines in your code. There is a standard for csv files that this naive approach doesn't follow: https://tools.ietf.org/html/rfc4180 – neves May 04 '16 at 23:13
15

Tony's answer generally works but doesn't handle the case where your text contains commas or quotes. You may prefer to use Workbook.SaveAs method.

Here is an example if you want to save the content of the Sheet1 as a separated csv file. 

Sub create_csv()
    Dim FileName As String
    Dim PathName As String
    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets("Sheet1")
    FileName = "filename.csv"
    PathName = Application.ActiveWorkbook.Path
    ws.Copy
    ActiveWorkbook.SaveAs FileName:=PathName & "\" & FileName, _
        FileFormat:=xlCSV, CreateBackup:=False
End Sub

Imagine that your Sheet1 contains :

lorem ipsum

lore,m ips"um"

The output csv file will be :

lorem,ipsum

"lore,m","ips""um"""

user3707264
  • 318
  • 4
  • 15
2

You may write a macro like to save the current workbook (opened excel file) in CSV from VBA:

ActiveWorkbook.SaveAs Filename:="C:\Book1.csv", _
    FileFormat:=xlCSVMSDOS, CreateBackup:=False
TiM
  • 15,812
  • 4
  • 51
  • 79
jatanp
  • 3,982
  • 4
  • 40
  • 46
  • Okay that would be a start. But any idea how I could do a completely external one ? – BuZz Dec 07 '11 at 17:50
0

For those writing the CSV manually, you need to handle commas, double quotes and new lines.

e.g.

Sub WriteToCsv(Items() as String)

    OutFile = FreeFile          
    Open "Outfile.csv" For Output As #OutFile
    
    Print #OutFile, "Header"
    
    For Each Item In Items       
        If InStr(1, Item, Chr(34)) > 0 Then Item = Chr(34) & Replace(Item, Chr(34), Chr(34) & Chr(34)) & Chr(34)
        If InStr(1, Item, ",") > 0 And Left(Item, 1) <> Chr(34) Then Item = Chr(34) & Item & Chr(34)
        If InStr(1, Item, vbLf) > 0 And Left(Item, 1) <> Chr(34) Then Item = Chr(34) & Item & Chr(34)
        Print #OutFile, Item
    Next
    
    Close OutFile

End Sub
Ian Botham
  • 113
  • 4
  • 11
0

Took your code as a basis (THANKS!!!) but had to modify it to make it work. It didn't handle multiple rows, all cells were put after each other. 2 loops: one to go through the rows and one to go through the cells of each row. Each time the row loop starts the temporary string is emptied. Before starting a new row, the temp string is added to the Outfile.

Sub ToCsv()

Dim rng As Range
Dim row As Range
Dim cell As Range

Dim ItemNew As String

Set rng = Range("A1:E2")    'Adjust the range accordingly

OutFile = FreeFile
Open "Outfile.csv" For Output As #OutFile

'Print #OutFile, "Header"

For Each row In rng.Rows
    ItemNew = ""
    For Each Item In row.Cells
        If InStr(1, Item, Chr(34)) > 0 Then Item = Chr(34) & Replace(Item, Chr(34), Chr(34) & Chr(34)) & Chr(34)
        If InStr(1, Item, ",") > 0 And Left(Item, 1) <> Chr(34) Then Item = Chr(34) & Item & Chr(34)
        If InStr(1, Item, vbLf) > 0 And Left(Item, 1) <> Chr(34) Then Item = Chr(34) & Item & Chr(34)
        If ItemNew = "" Then
            ItemNew = Item
        Else
            ItemNew = ItemNew & "," & Item
        End If
    Next
    Print #OutFile, ItemNew
Next
Close OutFile

End Sub
RvW
  • 1
  • 1