[SOLVED] vba code to open myscript.js through notepad, and replace specific texts inside by the value from spreadsheet

Issue

I want open myscript.js through notepad, and replace the specific texts inside by the values from worksheet. For example, inside myscript.js I have 6:7.400, 12:6.500, 24:7.000. I want to replace these 3 texts by the values from worksheet "Sheet1" range D2 to D4, say '6:7.100', '12:6.000', '24:7.200' without changes the format inside myscript.js. After replacement, I want to save myscript.js

I just tried open myscript.js through notepad, but no idea how to change the texts inside and how to save myscript.js,

Sub js()


    Dim pth As String
    
    pth = "K:\MVS\temp\myscript.js"

    Shell "Notepad.exe " & pth, vbNormalFocus
    

End Sub

Solution

VBA can read/write text file w/o other library.

Option Explicit
Sub ReplaceTextInFile()
    Dim srcFPath As String
    Dim desFPath As String
    Dim FileContent As String
    Dim NewContent As String
    Dim FileNumber As Integer
    Dim des_txt, src_txt, i As Integer
    src_txt = Array("AAA", "BBB", "CCC")
    des_txt = Sheets("Sheet1").Range("D2:D4").Value
    srcFPath = "D:\temp\template.js" ' update as needed
    desFPath = "D:\temp\myscript.js" ' update as needed
    If Dir(srcFPath) = "" Then
        MsgBox "File is not exists:" & srcFPath, vbExclamation
        Exit Sub
    End If
    ' Read js file
    FileNumber = FreeFile
    Open srcFPath For Input As FileNumber
    Do Until EOF(FileNumber)
        Line Input #FileNumber, FileContent
        NewContent = NewContent & FileContent & vbCrLf
    Loop
    ' Replace the content
    For i = 1 To UBound(des_txt)
        NewContent = Replace(NewContent, src_txt(i - 1), des_txt(i, 1))
    Next
    ' Update js file
    Close FileNumber
    FileNumber = FreeFile
    Open desFPath For Output As FileNumber
    Print #FileNumber, NewContent
    Close FileNumber
End Sub

template.js (AAA,BBB,CCC are placeholders for replacing)

console.log('AAA')
function test(c) {
  assert(c.constructor === Child)
console.log('BBB')
}
console.log('CCC')

---

Update

Question: Can you modify the codes a little bit further? Basically those Array #s inside myscript.js are from Sheets("Sheet1").Range("E2:E4").

Option Explicit
Sub ReplaceTextInFile()
    Dim FilePath As String
    Dim FileContent As String
    Dim NewContent As String
    Dim FileNumber As Integer
    Dim arr_txt, i As Integer
    arr_txt = Sheets("Sheet1").Range("D2:E4").Value
    FilePath = "D:\temp\myscript.js"
    If Dir(FilePath) = "" Then
        MsgBox "File is not exists:" & FilePath, vbExclamation
        Exit Sub
    End If
    ' Read js file
    FileNumber = FreeFile
    Open FilePath For Input As FileNumber
    Do Until EOF(FileNumber)
        Line Input #FileNumber, FileContent
        NewContent = NewContent & FileContent & vbCrLf
    Loop
    ' Replace the content
    For i = 1 To UBound(arr_txt)
        NewContent = Replace(NewContent, arr_txt(i,2), arr_txt(i,1))
    Next
    ' Update js file
    Close FileNumber
    FileNumber = FreeFile
    Open FilePath For Output As FileNumber
    Print #FileNumber, NewContent
    Close FileNumber
End Sub

Answered By - taller

Answer Checked By - Senaida (WPSolving Volunteer)