How can I edit a currently existing .csv file at specific rows in VBA?

Question

So I am trying to write some code in a workbook that will open a file in a certain filepath using VBA, find data that exists in a row with a certain row number and then overwrite those rows given that row number. I am aware of the "Open File For Input/Output/Append" functions but "Input" only reads, "Output" overwrites all the data in my file and "Append" only adds data to the end of the file. I have been stuck for a while now and could really use some help. Here is my current code segment:

Open Filepath For Output As #2
    ExternalRowCount = 0 ' Row number where I want to start writing data.
    ArrayRef = 0 ' Array Index for data

    Do Until EOF(1)
        ExternalRowCount = ExternalRowCount + 1
        If ExternalRowCount >= Found And ExternalRowCount < (Found + 100) Then ' looping through rows to get to my desired location ("Found" is my desired row number)
            CellData = arr1(ArrayRef)
            CellDataTwo = arr2(ArrayRef)
            Write #2, CellData, CellDataTwo
            ArrayRef = ArrayRef + 1
        End If
    Loop
Close #2

Any help would be greatly appreciated! Thank you

What is the file referenced in EOF(1)? Where do you read data from a file? — Tony Dallimore
– Tony Dallimore, Commented Oct 14, 2015 at 21:02
In general it is not possible to update a text file because a new record will be a different length from the old record. Normal practice is to loop reading records from file A and writing the changed records to file B. File B could have a new version number or file A could be renamed with suffix "Old" while file B is renamed to the original name of file A. — Tony Dallimore
– Tony Dallimore, Commented Oct 14, 2015 at 21:11

paul bica · Accepted Answer · 2015-10-14 23:41:28Z

3

This will update line 2 in Test.csv

Option Explicit

Public Sub updateCSVLine()
    Dim fName As String, fso As Object, fsoFile As Object, txt As Variant

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set fsoFile = fso.OpenTextFile("C:\Test.csv", 1)
    txt = fsoFile.ReadAll
    fsoFile.Close

    txt = Split(txt, vbNewLine)

    txt(2 - 1) = ".. valX, .. valY, .. valZ"

    Set fsoFile = fso.OpenTextFile("C:\Test.csv", 2)
    fsoFile.Write Join(txt, vbNewLine)
    fsoFile.Close
End Sub

Before:

l1 val1, l1 val2, l1 val3
l2 val1, l2 val2, l2 val3
l3 val1, l3 val2, l3 val3

After:

l1 val1, l1 val2, l1 val3
.. valX, .. valY, .. valZ
l3 val1, l3 val2, l3 val3

answered Oct 14, 2015 at 23:41

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

How can I edit a currently existing .csv file at specific rows in VBA?

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