Exporting Data into a CSV - Excel VBA

Question

Say I have a function that generates some data into cells into the current worksheet like :

Cells(1, "A").Value = ...
Cells(2, "A").Value = ...
Cells(3, "A").Value = ...
Cells(4, "A").Value = ...

Instead of the being the current worksheet in the current workbook, I want to create and load it into a csv file, to a give path

Say C:\USERS\Documents\Sample.csv.

I've seen stuff like

     ActiveWorkbook.SaveAs Filename:= _
"c:\MyFile.csv", FileFormat:=xlCSV _
, CreateBackup:=False

But this will just save the current workbook to another location, but I don't want to generate data in the current worksheet and then save, rather I want to export right away? Is there anyway I can do that. Maybe making like ActiveWorkbook = //pathname and then Activating it ?

Answer 1

You can write to a CSV quite simply using VBA. An example could be:

Sub WriteCSVFile()

Dim My_filenumber As Integer
Dim logSTR As String

My_filenumber = FreeFile

logSTR = logSTR & Cells(1, "A").Value & " , "
logSTR = logSTR & Cells(2, "A").Value & " , "
logSTR = logSTR & Cells(3, "A").Value & " , "
logSTR = logSTR & Cells(4, "A").Value

Open "C:\USERS\Documents\Sample.csv" For Append As #My_filenumber
    Print #My_filenumber, logSTR
Close #My_filenumber

End Sub