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 ?

First open a new workbook. Then have your macro deposit data in the new workbook. Then have you macro save the new workbook as csv in your folder of choice with your name of choise. Then close the new workbook. — Gary's Student, Oct 09 '13 at 15:20
@Gary'sStudent I'm using `Workbooks.Add` to create another workbook. How would I activate now to make sure im importing to it ... is something like `NewWorkbook.Sheets(1).Activate` ? — Thatdude1, Oct 09 '13 at 15:28
Don't use just workbooks.add... use something like... dim wb as workbook // Set wb = workbooks.add // with wb ... — Steve, Oct 09 '13 at 15:55

score 13 · Accepted Answer · answered Oct 09 '13 at 16:04

13

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

answered Oct 09 '13 at 16:04

Steve

1,620
2
19
33

6

This solution does not handle cases where whitespace or newlines or commas exist in your data. Always use a real CSV function rather than concatenating your own strings. – dataless Jul 09 '14 at 00:13
11

dataless... rather than hinting at a better solution and not really adding to the site... please, take the time to post your better solution. I am always happy to learn. Maybe, try not to knock others down, rather try to help them up. – Steve Jul 30 '14 at 19:23

score 9 · Answer 2 · answered Aug 19 '14 at 15:12

9

Use the .move to make a new book of the target sheet, then .saveas the newly created book as a CSV. Adjust the Pathname to adjust the directory where you want your csv saved.

    Pathname = "" & Thisworkbook.path & "YourName.csv"
    Sheets("Sheet you want as CSV").Move
    ActiveWorkbook.SaveAs Filename:=PathName, _
        FileFormat:=xlCSV, CreateBackup:=False

answered Aug 19 '14 at 15:12

CharlieSmith

311
4
12

You missing backslash at the path in front of the filename otherwise it will save at wrong place. I still consider this is the best answer. Thanks! – yancyn Jul 15 '22 at 08:50

score 1 · Answer 3 · answered Apr 25 '17 at 14:43

Just modified the code by @CharlieSmith to a fairly simple and more usable code, which will convert all the sheets in your workbook to new csv files named with respective sheet names.

Sub WriteCSVFile()
Dim i As Integer
Dim WS_Count As Integer

WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To WS_Count
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(i)
     PathName = "" & ThisWorkbook.Path & "\" & ws.Name & ".csv"
    ws.Copy
    ActiveWorkbook.SaveAs Filename:=PathName, _
        FileFormat:=xlCSV, CreateBackup:=False
Next i

End Sub

Hope this helps

Exporting Data into a CSV - Excel VBA

3 Answers3

Linked