Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create a separate CSV file from VBA?

Tags:

csv

excel

vba

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?
like image 348
BuZz Avatar asked Dec 07 '11 17:12

BuZz


2 Answers

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"""

like image 194
user3707264 Avatar answered Oct 11 '22 19:10

user3707264


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.

like image 36
Tony Dallimore Avatar answered Oct 11 '22 18:10

Tony Dallimore