CODEWITHSUNDEEP
vbacsvexcel
Thatdude1
Thatdude1

Reputation: 903

Exporting Data into a CSV - Excel VBA

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 ?

Upvotes: 11

Views: 101458

Answers (4)

Hassan Sial
Hassan Sial

Reputation: 1

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

Upvotes: -1

Praveen Agarwal
Praveen Agarwal

Reputation: 21

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

Upvotes: 2

CharlieSmith
CharlieSmith

Reputation: 321

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

Upvotes: 10

Steve
Steve

Reputation: 1638

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

Upvotes: 12

Related Questions