CODEWITHSUNDEEP
excelvba
Stuart Cruickshank
Stuart Cruickshank

Reputation: 65

VBA adding to an existing CSV file

I am trying to make some VBA that will add data to an existing CSV file, I have the below code but it overwrites the existing data instead of adding to it. I am guessing as it goes to the beginning of the file but am unsure how to force to add to the file.

Sub WriteCSV()
    Dim iLastRow As Long
    Dim iLastCol As Long
    Dim FilePath As String
    Dim Filename As String
    Dim Fullpath As String
    
    Fullpath = Worksheets("GUI").Range("f11")
    iLastRow = Range("A" & Rows.Count).End(xlUp).Row
    iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        Open Fullpath For Append As #1
            For i = 1 To iLastRow
               For j = 1 To iLastCol
                    If j <> iLastCol Then 'keep writing to same line
                        Write #1, Cells(i, j),
                    Else 'end the line
                        Write #1, Cells(i, j)
                    End If
                Next j
            Next i
           
        Close #1
       
    End Sub

Upvotes: 1

Views: 859

Answers (1)

Gove
Gove

Reputation: 1804

Excel works well well with CSV files as workbooks. This approach may solve your problem. Although, writing to the filesystem with VBA will be faster.

Sub writeCSV()
    Dim csv As Workbook
    Dim data_sheet As Worksheet
    
    Set data_sheet = ActiveSheet
    
    Set csv = Workbooks.Open(Worksheets("GUI").Range("f11").Value)
    data_sheet.Range("A1").CurrentRegion.Copy csv.Worksheets(1).Cells(csv.Worksheets(1).Rows.Count, 1).End(xlUp).Offset(1)
    
    csv.Close True

End Sub

Upvotes: 1

Related Questions