CODEWITHSUNDEEP
vbaexcelcsv
Awesomasaurus
Awesomasaurus

Reputation: 75

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

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

Upvotes: 0

Views: 7151

Answers (1)

paul bica
paul bica

Reputation: 10715

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

Upvotes: 3

Related Questions