find and replace string in text file (powershell script) with cell value with VBA-CodePudding

I am trying to create button in Excel which will call VBA script which will change text file.

I have text file which looks like:

String 1 
String2 
... 
String 123 
Invoke-WebRequest -Uri https://www.example.com/publicdocs/files/iceu/2022/07/IPE0729F.CSV.zip
-OutFile C:\folder\files\IPE0802F.CSV.zip

String 125 
.... 
String 999

What I need is to change string:

Invoke-WebRequest -Uri https://www.example.com/publicdocs/files/iceu/2022/07/ABB0729F.CSV.zip -OutFile C:\folder\files\ABB0802F.CSV.zip

and replace dates with values from cells, i.e.: replace 2022 with

Range("A1").Value

Replace 07 with

Range("A2").Value

Replace 0729 with Range("A3").Value

Replace 0802 With Range("A4").Value

So I need to find this string in text file, modify it and save file. Maybe someone can help with that since looks like I have no enough knowledge to achieve it (

Ok, here I can find needed string, but I dont know how to replace whole string with updated one:

Sub updatePS()

Const ForReading = 1, ForWriting = 2
Dim FSO, FileIn, FileOut, strTmp

Set FSO = CreateObject("Scripting.FileSystemObject")
Set FileIn = FSO.OpenTextFile("C:\powershell.ps1", ForReading)
Set FileOut = FSO.OpenTextFile("C:\powershell2.ps1", ForWriting, True)

Do Until FileIn.AtEndOfStream
    strTmp = FileIn.ReadLine
    If Len(strTmp) > 0 Then
        If InStr(1, strTmp, "Invoke-WebRequest", vbTextCompare) > 0 Then
              ' Here I should add code to replace updated string
        End If
    End If
Loop

FileIn.Close
FileOut.Close

End Sub

CodePudding user response：

Check out the vba Replace() function, You can set multiple replaces by: Replace(Replace(Replace(put here 3 replace conditions)))

CodePudding user response：

You did not answer all my clarification questions...

The the next solution assumes that "2022" and "07" are both between "/" two such characters and "0729" / "0802" have a following suffix of "F.C" string. Otherwise, the code could strictly replace the strings you asked for, but the possibility to also replace in different locations exists:

Sub ModifScript()
    Dim sh As Worksheet, fso As Object, ts As Object, sPath As String, sFile As String, strText As String
    
    Set sh = ActiveSheet
    sPath = ThisWorkbook.Path & "\" 'use here your real path, please!
    sFile = "powershell.ps1"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.getFile(sPath & sFile).OpenAsTextStream(1, -2)
        strText = ts.ReadAll
    ts.Close
    
    Dim chngTxt As String, chngText As String, frstChar As Long, endChar As Long
    frstChar = 1 'first character, where from InStr start evaluation
     frstChar = InStr(frstChar, strText, "Invoke-WebRequest") 'the digit number where the necessary string (to be changed) starts
    
     endChar = InStr(frstChar   Len("Invoke-WebRequest"), strText, "F.CSV.zip") 'the digit number where the necessary string ends
     'string to be processed:
     chngTxt = Mid(strText, frstChar, endChar   Len("F.CSV.zip") - frstChar)
       Debug.Print chngTxt   'just to visually check that the correct string to be changed has been returned
       
       chngText = Replace(Replace(chngTxt, "/2022/", "/" & sh.Range("A1").value & "/"), "/07/", "/" & sh.Range("A2").value & "/")
       chngText = Replace(Replace(chngText, "0729F.C", sh.Range("A3").value & "F.C"), "0802F.C", sh.Range("A4").value & "F.C")
       Debug.Print chngText 'just to visually check that the string to be changed has been correctly changed
       
     'replace the changed string in the original file text:
     strText = Replace(strText, chngTxt, chngText) 'the changed whole string!
      Debug.Print strText
      Dim scriptFold As String, pathToFile As String
        If Dir(sPath & "Scripts", vbDirectory) = "" Then MkDir sPath & "Scripts" 'create "Scripts" folder if it does not exist
        
        pathToFile = sPath & "Scripts\" & sFile
       Open pathToFile For Output As #1
            Print #1, strText
       Close #1
End Sub

The above code saves the processed file content in a new folder ("Scripts), created by the code, if not exists...

Basically, you should use the same script file, but always update the column B:B of the active sheet with the changed string (taken from A:A). It is necessary to only replace in code "/2022/" with "/" & Sh.Range("B1").value & "/", "/07/" with "/" & Sh.Range("B2").value & "/", "0729F.C" with Sh.Range("B2").value & "S.C" and so on...