Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Exporting Data into a CSV - Excel VBA

Tags:

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 ?

like image 507
Thatdude1 Avatar asked Oct 09 '13 15:10

Thatdude1


People also ask

How do I convert an Excel file to csv automatically?

In your Excel workbook, switch to the File tab, and then click Save As. Alternatively, you can press F12 to open the same Save As dialog. 2. In the Save as type box, choose to save your Excel file as CSV (Comma delimited).

Does VBA work on csv?

Macro to parse a csv or txt fileYou can copy the code and insert it into a VBA module. Just highlight it with the mouse, press CTRL+C and insert with CTRL+V. If you are viewing this page on a small screen, some of the code lines may appear "broken," but they will be okay when you paste into a VBA module.


3 Answers

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
like image 79
Steve Avatar answered Oct 02 '22 04:10

Steve


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
like image 30
CharlieSmith Avatar answered Oct 02 '22 04:10

CharlieSmith


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

like image 43
Praveen Agarwal Avatar answered Oct 02 '22 04:10

Praveen Agarwal