Append range to csv file

[jharaldson]

Hello all,
What I am trying to do is append a range on my spreadsheet to a csv file. I found this code, it works but it appends everything in one row. I want it to be in 2 columns in the csv file.
Any help would be awesome.

thanks

Sub Append2CSV()
Dim tmpCSV As String 'string to hold the CSV info
Dim f As Integer

Const CSVFile As String = "C:\test.csv" 'replace with your filename
f = FreeFile

Open CSVFile For Append As #f
tmpCSV = Range2CSV(Range("A1:B10"))
Print #f, tmpCSV
Close #f

End Sub

Function Range2CSV(list) As String
Dim tmp As String
Dim cr As Long
Dim r As Range

If TypeName(list) = "Range" Then
cr = 1

For Each r In list.Cells
If r.Row = cr Then
If tmp = vbNullString Then
tmp = r.Value 
Else
tmp = tmp & "," & r.Value
End If
Else
cr = cr + 1
If tmp = vbNullString Then
tmp = r.Value
Else
tmp = tmp & Chr(10) & r.Value
End If
End If
Next
End If

[Tinbendr]

Change

tmp = tmp & Chr(10) & r.Value

to

tmp = tmp & Chr(13) & r.Value

[jharaldson]

Thanks david but its not working. Here is what i am trying to accomplish:

A B
text text
text text

I want the the csv file to read as;
text,text
tex, text

but with the set up now it is texttexttexttexttext

thanks again

[Tinbendr]

The statement to add the value to the function is missing.

    End If
    Range2CSV = tmp
    End Function

This is how I code it.

Function MyOption(list As Range) As String

Dim A As Long
Dim B As Long
Dim NumRows As Long
Dim NumCols As Long
Dim Tmp As String

NumRows = list.Rows.Count
NumCols = list.Columns.Count

For A = 1 To NumRows
    For B = 1 To NumCols
        Tmp = Tmp & Cells(A, B) & ","
    Next
    Tmp = Left(Tmp, Len(Tmp) - 1)
    Tmp = Tmp & Chr(13)
Next

MyOption = Tmp
End Function

[jharaldson]

test.csv here is a csv sample that i get when i run your code and my original code. This text here sample,text,,,,,,,,,,, is what the original file is and when i append i get whats right below it. I want it to look like the original.

thanks again

[Tinbendr]

Here's the sample file I created. It generates the row/column as the excel sheet. Look at it with Wordpad.