11

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 ?

Improve this question
3
  • 3
    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. 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 ? Commented Oct 9, 2013 at 15:28
  • 2
    Don't use just workbooks.add... use something like... dim wb as workbook // Set wb = workbooks.add // with wb ... Commented Oct 9, 2013 at 15:55

4 Answers 4

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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... 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.
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
Improve this answer

1 Comment

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!
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

Improve this answer

Comments

-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
Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.