How to create a separate CSV file from VBA?
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'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"""