excel - trying to store text file rows in VBA

Question

Greetings, I'm hoping for help in figuring out how to store each row of a text file read into a VBA program as a string. I want to modify one of the strings and then put them all back together, but do not know how to read through a text file and store each row as a separate variable in an intelligent way. Thanks for any help you can provide!

Answer 1

If you don't want to add references, you could just go with straight vba code.

Take for instance the following file wordlist.txt:

realize
empty
theorize
line
socialize
here
analyze

The following code uses two methods to do as you described (one more common than the other):

Option Explicit

Sub main()
    Dim sFileName As String
    Dim sMergedLineArray() As String
    Dim sTextToFind As String
    Dim sReplacementText As String
    Dim sOutputFile As String
    Const MY_DELIMITER = "|"

    sFileName = "C:deletemewordlist.txt"

    sMergedLineArray = ReadFileIntoArray(sFileName)

    sTextToFind = "ze"
    sReplacementText = "se"

    'Loop through each value in the array and make a change if you need to
    Dim x As Integer
    For x = 0 To UBound(sMergedLineArray)
        If InStr(1, sMergedLineArray(x), sTextToFind, vbTextCompare) > 0 Then
            sMergedLineArray(x) = Replace(sMergedLineArray(x), sTextToFind, sReplacementText, 1, -1, vbTextCompare)
        End If
    Next x

    sOutputFile = "C:deletemeUK_Version.txt"
    If Not SpitFileOut(sOutputFile, sMergedLineArray) Then
        MsgBox "It didn't work :("
    End If


    'OR...put it all together, make a mass change and split it back out (this seems unlikely, but throwing it in there anyway)
    sTextToFind = "se"
    sReplacementText = "ze"
    Dim sBigString As String
    Dim sNewArray As Variant
    sBigString = Join(sMergedLineArray, MY_DELIMITER)
    sBigString = Replace(sBigString, sTextToFind, sReplacementText, 1, -1, vbTextCompare)

    sNewArray = Split(sBigString, MY_DELIMITER, -1, vbTextCompare)

    sOutputFile = "C:deletemeAmerican_Version.txt"
    If Not SpitFileOut(sOutputFile, sNewArray) Then
        MsgBox "It didn't work"
    End If

    MsgBox "Finished!"

End Sub


Function ReadFileIntoArray(sFileName As String) As String()
    Dim sText As String
    Dim sLocalArray() As String
    Dim iFileNum As Integer
    Dim iLineCount As Integer

    iFileNum = FreeFile

    Open sFileName For Input As #iFileNum
    Do Until EOF(iFileNum)
        Input #iFileNum, sText
        ReDim Preserve sLocalArray(iLineCount)
        sLocalArray(iLineCount) = sText
        iLineCount = iLineCount + 1
    Loop

    Close #iFileNum

    ReadFileIntoArray = sLocalArray


End Function


Function SpitFileOut(sFileName As String, sMyArray As Variant) As Boolean
    Dim iFileNum As Integer
    Dim iCounter As Integer

    SpitFileOut = False

    iFileNum = FreeFile

    Open sFileName For Output As #iFileNum
        For iCounter = 0 To UBound(sMyArray)
            Print #iFileNum, sMyArray(iCounter)
        Next
    Close #iFileNum


    SpitFileOut = True
End Function

If you run the main sub, you'll end up with two files:

• UK_Version.txt: This is the result of the first method
• American_Version.txt: This is the result of the second

There's lesson 1 of VBA, young Padawan; absorb it, learn and change your login name :P