Is there a way to find and replace in a .txt or .xml file from VBA without using REGEX?

Question

I have an excel file that runs a model from an external .exe file, and collects its outputs to store on various sheets for data analysis.

The .exe file keeps its input files in an accessible text based format. I realised that I can create a batch run by editing the input files directly from vba and running in a loop, however it requires find and replace in a .txt or .xml file from VBA. I would need to use a wildcard as part of the string I want to find will change everytime and is unpredictable at the first iteration. E.g:

I want to find the following text within the text based file:

"0.3843 Flow_Rate_Mass kg/s"

and replace the number before rerunning my script to solve the .exe with this new input file for a new flow rate. However, the number 0.3843 could be anything in the first instance (in the format "x.xxxx Flow_Rate_Mass kg/s"), so I need to use a wildcard in order to find and replace.

After the first iteration I will have set this value at the previous iteration, and so I can use find and replace from a known string without needing a wildcard.

So the following should work, but is not flexible enough at the first iteration:

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String

sFileName = "C:\filelocation"

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

sTemp = Replace(sTemp, "0.3843 Flow_Rate_Mass kg/s", "0.5000 Flow_Rate_Mass kg/s")

iFileNum = FreeFile
Open sFileName For Output As iFileNum

Print #iFileNum, sTemp

Close iFileNum

Is there any work around without REGEX?

If you know _Flow_Rate_Mass kg/s_ will always be present use Instr to find it and go back x number of characters (assuming fixed width) and then select Left and Right of those two numbers. I can write up that and a regex if you think will meet requirements. — QHarr, Sep 10 '19 at 15:25
Code can always do what regex does. The question is, how much code do you want that to be. — Mathieu Guindon, Sep 10 '19 at 15:30
@QHarr I don't know REGEX (i'm self taught in VBA, a mechanical engineer, not a programmer) and don't really have the time to learn in this instance - was planning to implement this in a few hours before finding this issue. Your second suggestion may well do the trick. I will check it for fixed width tomorrow morning. — Petrichor, Sep 10 '19 at 15:37

QHarr · Answer 1 · 2019-09-10T16:45:31.820

An example regex replace might be

Option Explicit

Public Sub test()
    Dim i As Long, tests()

    tests = Array("some text here 0.3843 Flow_Rate_Mass kg/s other text", "some text here 0.5000 Flow_Rate_Mass kg/s other text")
    For i = LBound(tests) To UBound(tests)
        Debug.Print ReplaceMatch(tests(i))
    Next
End Sub

Public Function ReplaceMatch(ByVal inputString As String) As String
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    With re
        .Global = True
        .MultiLine = True
        .pattern = "\b[\.0-9]+( Flow_Rate_Mass kg\/s)"

        If .test(inputString) Then
            ReplaceMatch = .Replace(inputString, "$1")
        Else
            ReplaceMatch = inputString
        End If
    End With
End Function

Regex explanation:

An Instr example assuming fixed length of that flow rate value. Note that String functions do have max lengths they can work with.

Option Explicit

Public Sub test()
    Dim i As Long, tests()

    tests = Array("some text here 0.3843 Flow_Rate_Mass kg/s other text", "some text here 0.5000 Flow_Rate_Mass kg/s other text")
    For i = LBound(tests) To UBound(tests)
        Debug.Print ReplaceMatch(tests(i))
    Next
End Sub

Public Function ReplaceMatch(ByVal inputString As String) As String
    Dim pos As Long, fixedLength As Long, length As Long, leftPosition As Long, rightPosition As Long
    fixedLength = 6
    pos = InStr(inputString, " Flow_Rate_Mass kg/s")
    If pos > 0 Then
        length = Len(inputString)
        leftPosition = pos - fixedLength - 1
        rightPosition = length - pos
        ReplaceMatch = Left$(inputString, leftPosition) & Right$(inputString, rightPosition)
    Else
        ReplaceMatch = inputString
    End If
End Function

Note that there are faster ways using command line/powershell to do this and also existing tools:

examples 1, 2

Is there a way to find and replace in a .txt or .xml file from VBA without using REGEX?

1 Answers1