Q:

Get value between two commas using VBA

I want to edit a text file by using Excel data. The text file has the form:
Data1, Number1, Number2, ..., etc.

I want to get Number1 and replace sum value(Number1+excel data). Number2 is also the same flow.  
What is the best way of doing this?

A:

This sample reads in a text file, get every value between commas and sum it with cell A1's value 100
Input file (C:\test.txt)
data1,1,2,3,4,5
data2,1,2,3,4,5

Excel data (Sheet 1)
[A1] = 100

Code
Sub ReadCommas()

    strPath = "C:\test.txt"
    Dim arVal() As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set objFile = fso.OpenTextFile(strPath)

    Do While Not objFile.AtEndOfStream
        strLine = objFile.ReadLine
        arVal = Split(strLine, ",")

        For i = 1 To UBound(arVal)
            arVal(i) = Val(arVal(i)) + Sheets(1).Cells(1, 1)
            Debug.Print arVal(i)
        Next i

    Loop
    objFile.Close

End Sub

Output (VBA editor » Immediate Window)
101
102
103
104
105
201
202
203
204
205

