0

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
Improve this question
15
  • Will all the time strings to be replaced be the same? I mean, if you make the change and save the file, it will remain with the new data. Shouldn't it be better to use it as a kind of template and save the modified content in another folder (with the same name)? Commented Aug 3, 2022 at 11:41
  • Yes, strings to be replaced will be same. THere couple of strings in the scripts where date has to be updated daily. If I got you correct you mean that every time updated file will be saved in some folder with new name? I am ok to save in new folder, but name should be the same to be able to schedule script to run automatically. Commented Aug 3, 2022 at 11:46
  • I think it is quite easy to make kind of template, let say write script in cells and update cell values and then save result in text file, but i find it not very nice solution, but probably will use it if will not get help here Commented Aug 3, 2022 at 11:48
  • I said "with the same name"... Now, will all the time "ABB" be before "0729" and " F.CSV.zip" after? The same for "IPE" in front of 0802 and "F.CSV.zip" after? Commented Aug 3, 2022 at 11:49
  • I was trying to say something different, respectively: The actual script to be consider a kind of template and create a piece of code to process it as you need. Otherwise, the code will work only once! If the file will be saved, next time "\07\", "0729" and "0802" will not exist, anymore to be used as references when run the code next time... I do not know how you handle the actual script, of course. But I will try helping with a piece of code only if I can imagine a scenario to make something useful, not just didactic, for one use... Commented Aug 3, 2022 at 11:54

2 Answers 2

Reset to default
1

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, frstChar2 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
      frstChar2 = InStr(frstChar + Len("Invoke-WebRequest"), strText, "OutFile C:\") 'this returns the real ending of the necessary string...
      
     endChar = InStr(frstChar2 + 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...

Edited:

Please, test the next variant. It does not need the existing strings to be replaced, anymore. This code is able to firstly identify/extract the strings to be replaced and then replaces them with the one from the Excel sheet:

Sub ModifScript_Next()
    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 & "\"
    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, frstChar2, 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
      frstChar2 = InStr(frstChar + Len("Invoke-WebRequest"), strText, "OutFile C:\") 'this returns the real ending of the necessary string...
      
     endChar = InStr(frstChar2 + 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
       Dim frstStr As String, secStr As String, thrdStr As String, fourStr As String, frstC As Long, secCh As Long
        'extract first string:
         frstC = InStr(1, chngTxt, "docs/files/iceu/")
          secCh = InStr(frstC + Len("docs/files/iceu/"), chngTxt, "/")
           frstStr = Mid(chngTxt, frstC + Len("docs/files/iceu/"), secCh - (frstC + Len("docs/files/iceu/")))
           'Debug.Print "_" & frstStr & "_"  'OK
           
        'extract second string:
        frstC = secCh + 1
         secCh = InStr(frstC, chngTxt, "/IPE")
           secStr = Mid(chngTxt, frstC, secCh - frstC)
           'Debug.Print "_" & secStr & "_"  'OK
           
        'extract third string:
        frstC = secCh + Len("/IPE")
         secCh = InStr(frstC, chngTxt, "F.CSV")
          thrdStr = Mid(chngTxt, frstC, secCh - frstC)
          'Debug.Print "_" & thrdStr & "_": 'Stop 'OK
          
        'extract fourth string:
        frstC = InStr(secCh + Len("F.CSV"), chngTxt, "files\IPE")
         secCh = InStr(frstC + Len("files\IPE"), chngTxt, "F.CSV")
          fourStr = Mid(chngTxt, frstC + Len("files\IPE"), secCh - (frstC + Len("files\IPE")))
          'Debug.Print "_" & fourStr & "_": Stop 'OK
    
       chngText = Replace(Replace(chngTxt, "/" & frstStr & "/", "/" & sh.Range("A1").value & "/"), "/" & secStr & "/", "/" & sh.Range("A2").value & "/")
       chngText = Replace(Replace(chngText, thrdStr & "F.C", sh.Range("A3").value & "F.C"), fourStr & "F.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

After testing it and concluding that it fits what you want, you can overwrite the existing file in the last lines. I mean, pathToFile must be sPath & sFile. And, of course, in such a case the sequence using a different folder should be eliminated.

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

@Роман Рослый But you should use "C:\" for sPath. The necessary full name is obtained by concatenation of sPath with sFile... This split is necessary to create (in sPath) a new folder ("Scripts"), where to create the new .ps1 file. At least, to let you check if it is correctly processed... I am asking myself if your user has enough permissions to write on C... That's why I've chosen the path of the workbook where the code runs...
Yes it works! So the idea is to keep first file as a template and script will always work with this file and will create new file with correct string? Did I get it right?
@Роман Рослый If nobody needs to modify the template and you only need to replace the strings you asked for, you must only place the respective values in the appropriate cells. Then, processing of the .ps1 file will be done from its new path.
Confirm - all works just fine! I mentioned "path" just to make it easier to copy/paste here, real string was provided earlier. I made small change and can see that everything works correctly. Thanks you very much @FaneDuru!!!
Hi @FaneDuru, , when I used your code I didnt notice that string was a bit different from original string I used in the script (I removed some part of path in https link. So when I made comment that code gives wrong result and used "path" word you pointed that there should be exact much so I re-checked whole string and found difference between part of the string in your code and original string so I just changed that and it started to work perfect. So as I said - your code works just fine and there was my bad. Thanks again.
|
0

Check out the vba Replace() function, You can set multiple replaces by:

Replace(Replace(Replace(put here 3 replace conditions)))

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.