Export to csv

[Andrew01]

Hi,

Wondering if someone could help me as I'm quite new to VBA. I have an .xlsm file and a .csv file and I am trying to export a 5 values ( eg Range("A1:E1)) from the xlsm workbook to the csv, in the following format:

Value1,Value2,Value3,Value4,Value5

I know this should be quite simple but I'm a newbie, any help would be greatly appreciated

[Tinbendr]

Sub ExportCells()
    Dim aCell As Range
    Open "MyFile.csv" For Output As #1
    For Each aCell In ActiveSheet.Range("A1:E1")
        Write #1, aCell
    Next
    Close #1
End Sub

Sub ImportCells()
    Dim MyValue As Variant
    Dim aCell As Range
    Open "MyFile.csv" For Input As #1
    For Each aCell In ActiveSheet.Range("A1:E1")
        Input #1, MyValue
        aCell = MyValue
    Next
    Close #1
End Sub

[Andrew01]

Hey David thanks that works great.
One thing though, it exports the values one by one down the first column eg
Value1
Value2
Value3
Value4
Value5
Any simple way to achieve the following format?
Value1,Value2,Value3,Value4,Value5

Thanks again for your help!

[snb]

sub export_snb()
    createobject("scripting.filesystemobject").createtextfile("G:\OF\example.csv").write join(application.index(range("A1:E1"),1,0),",")
end sub

sub import_snb()
    workbooks.open "G:\OF\example.csv"
end sub

[Andrew01]

I appreciate your help snb, however I think your method is a bit over my head as I am new to VBA. If anyone could suggest a way to modify the code tinbendr sent through so as to achieve the comma seperated format required I would be extremely grateful as I am pulling my hair out!

Thanks heaps.

[Tinbendr]

Sub ExportCells()
    Dim aCell As Range
    Dim Temp$
    Open "MyFile.csv" For Output As #1
    For Each aCell In ActiveSheet.Range("A1:E1")
        Temp$ = Temp$ & aCell.Text & ","
    Next
    Temp$ = Left(Temp$, Len(Temp$) - 1)
    Print #1, Temp
    Close #1
End Sub

Sub ImportCells()
    Dim MyValue As Variant
    Dim aCell As Range
    Open "MyFile.csv" For Input As #1
    Line Input #1, MyValue
    ActiveSheet.Range("A1:E1").Value = Split(MyValue, ",")
    Close #1
End Sub

[Andrew01]

This is much better however it outputs the values in adjacent cells:
Value1 Value2 Value3 etc
Rather than having all the values in the one cell seperated by a comma. Thanks for the help, I'm almost there. Any ideas?

[BrianMH]

Are you sure? If you edit the CSV file in notepad it should show it as separated by comas. Excel handles that delimiter automatically so will show as normal.

[Tinbendr]

You lost me.

[Andrew01]

Hey your right thats great! Thanks heaps guys.
Just a quick one. I now have over a hundred values that I want to export to the csv. When I open the csv in a text editor (notepad), would it be possible to show for example 4 values seperated by commas on each line? Just for greater clarity. eg.
Value1,Value2,Value3,Value4
Value5,Value6,Value7,Value8
etc.
Cheers

[BrianMH]

I'm assuming you're doing a range of rows. So you want it to output each row as a seperate line in your csv file. If so this will do that.

Sub ExportCells()
    Dim aCell As Range
    Dim aRow As Long
    Dim aRange As Range
    Dim Temp$
    Set aRange = ActiveSheet.Range("b1:E50")
    Open "C:\MyFile.csv" For Output As #1
    For aRow = 1 To aRange.Rows.Count
        For Each aCell In aRange.Rows(aRow).Cells
            Temp$ = Temp$ & aCell.Text & ","
        Next aCell
        Temp$ = Left(Temp$, Len(Temp$) - 1)
        Temp$ = Temp$ & Chr(13) & Chr(10)
    Next aRow
    Print #1, Temp
    Close #1
End Sub

[Andrew01]

Hey BrianMH thanks so much that does exactly what I needed! Thankyou all for all your help, deeply appreciated. Finally can get some sleep now