Excel VBA: Copy Data From Excel to END of Existing .csv File

What VBA code do I need to copy a given range in Excel and paste to the END of an already existing .csv file WITHOUT iterations?

Assumptions:
*Data From=  "C10:AA5000" in "Sheet1" of a workbook titled: "FileWithData.xlsm"
*Data To: "C:\FileStorage\ImportedData.csv" <---Already exists
ouestqueAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,
pls try
Sub writeAppendCSV()
' REFERENCE MIcrosoft ActiveX DataObjects
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    strFile = Workbooks("FileWithData.xlsm").FullName

    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & _
            ";Extended Properties = 'Excel 12.0 Xml;HDR=NO';"

    cn.Open strCon
    strRange = "C10:AA5000"
    strSQL = "SELECT * FROM [Sheet1$" & strRange & "] AS T1"
    rs.Open strSQL, cn
    strData = rs.GetString(, , ",", Chr(10))
    strData = Left(strData, Len(strData) - 1)
    Debug.Print strData
    rs.Close
    Set rs = Nothing: Set cn = Nothing
    ff = FreeFile
    Open "C:\temp\ImportedData.csv" For Append As #ff
    Print #ff, strData

    Close #ff
End Sub

Open in new window

Regards
0
 
ouestqueAuthor Commented:
Thanks again Rgonzo!! So Cool!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.