0

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?

0

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
0

I got value Number1 and Number2 as I wanted with the following Code:

Input file (C:\intest.txt)

Data1, 1, 2, ...etc
Data2, 3, 4, ...etc
...  
Data100, 200, 300, ...etc

Excel data (Sheet 1)

[A1] = 100, [B1] = 200
[A2] = 300, [B2] = 400
...  
[A100] = 2000, [B100] = 3000

Code

Sub ButtonClick()

    strPath = "C:\intest.txt"    

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

    Do Until objFile.AtEndOfStream
        strLine = objFile.ReadLine        
        '※1 means Start Position of Num1
        intNum1Cnt = (InStr(※1, strLine, ",")) - ※1    'Count of Number1 of Data1:1
        dblNum1 = Mid(strLine , ※1, intNum1Cnt )        'Value of Number1 of Data1:1
        dblExcelNum1 = GetValue1()                       'Value of [A1]:100
        dblSumNum1 = dblNum1 + dblExcelNum1              'Sum:101
        'Replace dblSumNum1 to dblNum1 in OutputFile

        '※2 means Start Position of Num2
        ※2 = ※1 + intValue1Cnt + 1
        intNum2Cnt = InStr(※2, strLine, ",") - ※2       'Count of Number2 of Data1:1
        dblNum2 = Mid(strLine, ※2, intNum2Cnt)           'Value of Number2 of Data1:2
        dblExcelNum2 = GetValue2()                        'Value of [B1]:200            
        dblSumNum2 = dblNum2 + dblExcelNum2               'Sum:202
        'Replace dblSumNum2 to dblNum2 in OutputFile    
    Loop
    objFile.Close
End Sub


Function GetValue1() As Double

    For i = 1 To 100
        strCellValue1 = Sheets(1).Cells(i, 1).Value
        GetValue1 = CDbl(strCellValue1)            
    Next i  
End Function


Function GetValue2() As Double

    For j = 1 To 100
        strCellValue2 = Sheets(1).Cells(j, 2).Value
        GetValue2 = CDbl(strCellValue2)            
    Next j  
End Function

Output File (C:\outtest.txt)

Data1, 101, 202, ...etc
Data2, 303, 404, ...etc
...  
Data100, 2200, 3300, ...etc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.