Find and Replace text with tabs and line breaks in VBA

aadya bajaj 0
Viewed 29

Being a beginner at coding, I am trying to replace part of the code in my Python script using VBA. The challenge I face is that I need to replace 2 lines of code with nothing, and the VBA is not able to "find" these 2 lines in the code at all, which I think is because of the spaces, tabs in the python script. Below is the code that I have tried:

Attempt 1

strContents = Replace(strContents, "if time == 12:" & vbNewLine & vbTab & "Freq = 1", "") ***' THIS IS THE MOST CRUCIAL LINE - WHICH IS FAILING RIGHT NOW***

I am not adding the rest of the code of finding and replacing as it works seamlessly, and the issue is with being able to find this particular expression.

The python script I am trying to delete (or replace with nothing), looks like this:

            if time == 12:
                Freq = 1
            else:
                Freq = 12

In another attempt, I also tried counting the number of spaces, and asking the VBA to find the text in the python script with the number of spaces I could count in the script.

I may be missing something really basic - so please feel free to give your inputs. Your help would be greatly appreciated!

Many thanks in advance. :)

find replace vba
1 Answers

Thanks @Aldert for responding, here is the entire code :

 Sub FindReplaceTrials()

Dim objFSO
Const ForReading = 1
Const ForWriting = 2
Dim objTS 'define a TextStream object
Dim strContents As String
Dim path As String
Dim fileSpec As String
Dim filename As String

path = Application.ActiveWorkbook.path

For m = 4 To 11 ' we need to make this dynamic too
    filename = Worksheets("ScriptName").Cells(m, 1).Value
    fileSpec = path & "\" & filename & ".py"
    'MsgBox (vbCrLf & vbTab & "else:")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTS = objFSO.OpenTextFile(fileSpec, ForReading)
    If Worksheets(4).Range("B" & 7).Value = 1 Then
        If filename = "econ" Then
            strContents = objTS.ReadAll
            strContents = Replace(strContents, "if time == 12:" & vbCrLf & Space(20) & "freq = 1" & vbCrLf & Space(16) & "else:" & vbCrLf & Space(20) & "freq = 12", "freq = 12")
        Set objTS = objFSO.OpenTextFile(fileSpec, ForWriting)
        objTS.Write strContents
        objTS.Close
            End If
    End If
    objTS.Close
    Next

End Sub

The vbCrLf & Space() objects worked to find the right sentences in the script.

aadya bajaj0
Related