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
– Gary's Student, Commented Oct 9, 2013 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
– Thatdude1, Commented Oct 9, 2013 at 15:28
Don't use just workbooks.add... use something like... dim wb as workbook // Set wb = workbooks.add // with wb ... — Steve
– Steve, Commented Oct 9, 2013 at 15:55

Steve · Accepted Answer · 2013-10-09 16:04:20Z

12

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 9, 2013 at 16:04

Steve

1,6482 gold badges19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dataless Over a year ago

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.

Steve Over a year ago

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.

CharlieSmith · Accepted Answer · 2014-08-19 15:12:55Z

11

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, 2014 at 15:12

CharlieSmith

3314 silver badges13 bronze badges

1 Comment

yancyn Over a year ago

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!

Praveen Agarwal · Accepted Answer · 2017-04-25 14:43:56Z

2

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

answered Apr 25, 2017 at 14:43

Praveen Agarwal

212 bronze badges

Comments

Fedor · Accepted Answer · 2024-12-27 11:07:00Z

Sub GenerateDataToCSV()
    Dim ws As Worksheet
    Dim i As Long
    Dim StudentCSVPath As String
    Dim FacultyCSVPath As String

    ' File paths for CSV files
    StudentCSVPath = ThisWorkbook.Path & "\StudentData.csv"
    FacultyCSVPath = ThisWorkbook.Path & "\FacultyData.csv"

    Application.ScreenUpdating = False

    ' Generate StudentData worksheet
    Set ws = Worksheets.Add
    ws.Name = "StudentData"

    With ws
        .Cells(1, 1) = "StudentID"
        .Cells(1, 2) = "IsImproved"
        .Cells(1, 3) = "GPA"
        .Cells(1, 4) = "Retained"
        .Cells(1, 5) = "SatisfactionScore"
        .Cells(1, 6) = "AdminCost"
        .Cells(1, 7) = "ServicesUsed"
    End With

    For i = 2 To 1001
        With ws
            .Cells(i, 1) = i - 1
            .Cells(i, 2) = IIf(Rnd() < 0.5, "Yes", "No")

            If .Cells(i, 2) = "Yes" Then
                .Cells(i, 3) = Round(2.8 + Rnd() * 0.8, 2)
                .Cells(i, 4) = IIf(Rnd() < 0.85, "Yes", "No")
                .Cells(i, 5) = Round(3.7 + Rnd() * 1, 1)
                .Cells(i, 6) = Round(7500 + Rnd() * 1000, 0)
                .Cells(i, 7) = Int(3 + Rnd() * 4)
            Else
                .Cells(i, 3) = Round(2.4 + Rnd() * 0.8, 2)
                .Cells(i, 4) = IIf(Rnd() < 0.75, "Yes", "No")
                .Cells(i, 5) = Round(3 + Rnd() * 1, 1)
                .Cells(i, 6) = Round(9500 + Rnd() * 1000, 0)
                .Cells(i, 7) = Int(1 + Rnd() * 3)
            End If
        End With
    Next i

    ' Export StudentData to CSV
    ws.Copy
    ActiveWorkbook.SaveAs Filename:=StudentCSVPath, FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=False

    ' Generate FacultyData worksheet
    Set ws = Worksheets.Add
    ws.Name = "FacultyData"

    With ws
        .Cells(1, 1) = "FacultyID"
        .Cells(1, 2) = "IsImproved"
        .Cells(1, 3) = "Publications"
        .Cells(1, 4) = "Citations"
        .Cells(1, 5) = "GrantFunding"
    End With

    For i = 2 To 101
        With ws
            .Cells(i, 1) = i - 1
            .Cells(i, 2) = IIf(Rnd() < 0.5, "Yes", "No")

            If .Cells(i, 2) = "Yes" Then
                .Cells(i, 3) = Int(2 + Rnd() * 2)
                .Cells(i, 4) = Int(.Cells(i, 3).Value * (10 + Rnd() * 10))
                .Cells(i, 5) = Round(600000 + Rnd() * 300000, -3)
            Else
                .Cells(i, 3) = Int(1 + Rnd() * 2)
                .Cells(i, 4) = Int(.Cells(i, 3).Value * (5 + Rnd() * 10))
                .Cells(i, 5) = Round(400000 + Rnd() * 200000, -3)
            End If
        End With
    Next i

    ' Export FacultyData to CSV
    ws.Copy
    ActiveWorkbook.SaveAs Filename:=FacultyCSVPath, FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=False

    Application.ScreenUpdating = True
    MsgBox "Data generation and export to CSV complete! Files are saved at: " & vbCrLf & _
           StudentCSVPath & vbCrLf & FacultyCSVPath
End Sub

Please describe what is different to the other given answers. Your code does much more things (e.g. create dummy data and create two export/datasets) than asked for.

Collectives™ on Stack Overflow

Exporting Data into a CSV - Excel VBA

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related