VBA adding to an existing CSV file

Question

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

Your code works as is for me and appends the data to the existing file. — PeterT
– PeterT, Commented Apr 19, 2022 at 16:13

Gove · Accepted Answer · 2022-04-20 12:52:40Z

1

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

answered Apr 20, 2022 at 12:52

Gove

1,82411 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA adding to an existing CSV file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related