How to create a separate CSV file from VBA?

Is something like this what you want?

Option ExplicitSub 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 OutputFileNumEnd 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:=FalseEnd Sub

Imagine that your Sheet1 contains :

lorem ipsum

lore,m ips"um"

The output csv file will be :

lorem,ipsum

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

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