Autogenerating document from documents and excel sheets-CodePudding

I'm new to VBA in excel, but trying to learning. My hopes are to autogenerate a text document in the following way:

1: Read a cell (cell_1) i excelsheet, eg. "type".

2: Open textfile also named "type" and create a new textfile "new_type".

3: Find string in the new textfile "new_type"

4: Replace string in new textfile "new_type" with string from another cell (cell_2) in the excelsheet

I want to repeat this proces in a loop of sorts, with a case select to find which document to open, but Im having problems pulling it all together.

Here is what I've been trying so far:

Sub ReplaceStringInFile()

    Dim objFSO As Object, objFil As Object, objFil2 As Object
    Dim strFileName As String, StrFolder As String, strAll As String, newFileText As String

    Set objFSO = CreateObject("scripting.filesystemobject")
    StrFolder = "C:\Users\User\Documents\folder\"
    strFileName = Dir(StrFolder & "document.txt")

    Do While strFileName <> vbNullString
        Set objFil = objFSO.opentextfile(StrFolder & strFileName)
        strAll = objFil.readall
        objFil.Close
        Set objFil2 = objFSO.createtextfile(StrFolder & strFileName)
        newFileText = Replace(strAll, "old_string", "string_from_specific_cell")
        objFil2.Write newFileText
        objFil2.Close
        strFileName = Dir
    Loop
End Sub

Here I replace a string in my old document, but I wanted to keep the old document intact and create new document to modify.

CodePudding user response：

The following code can be used as a subroutine to alter a file in the manner you've requested:

Sub Create_altered_copy_of_file(StrFolder, strFileName, strPrefix, strOldText, strNewText)
    Set objFSO = CreateObject("scripting.filesystemobject")
    Set objFilRead = objFSO.opentextfile(StrFolder & strFileName)
    Set objFilWrite = objFSO.createtextfile(StrFolder & strPrefix & strFileName)
    objFilWrite.Write Replace(objFilRead.readall, strOldText, strNewText)
    objFilRead.Close
    objFilWrite.Close
End Sub

You can call it like so:

Create_altered_copy_of_file "C:\YourFolder\","TheFilename.txt","New_","a","1"

The above would create a copy of TheFilename.txt in the same folder, called New_TheFilename.txt - where all letter a occurrences are replaced with 1.

If your question could be edited with a screenshot or mock-up of your input table, we could also come up with a loop to read it and use it with the sub above.