Tom’s Tutorials For Excel: Exporting Code to Find and Replace
Following up last week’s blog entry about exporting code from a module to a text file, here is how to create a copy of that text file and make changes to the code for Find and Replace.
The following macro transfers code from Module1 of the active workbook, and copies it to an existing (and closed) text file named “YourFileName.txt” located in C:\YourFilePath\.
The macro builds a string in memory that is the text in “YourFileName.txt” while finding and replacing the items you specify (“myVariableName” to “myChangedVariableName” in this example).
Then, that string is placed into a new text file that gets created on the fly, in this example “YourFileNameCopy.txt”.
Finally, that new created text file named “YourFileNameCopy.txt” is opened and maximized to show you the result of the Module1 code with the find and replace action completed. The original text file of “YourFileName.txt” remains closed but with the original unfound and unreplaced text.
I used Notepad as the text editor. A reference to VBA Extensibility 5.3 beforehand is required, as shown in the above-linked blog post.
Sub ExportModuleFindReplace() Dim arr() As String Dim iCounter As Integer Dim strSource As String, strTarget As String, strTextFind As String Dim strTextReplace As String, strText As String, strPath As String strPath = "C:\YourFilePath\" strSource = strPath & "YourFileName.txt" strTarget = strPath & "YourFileNameCopy.txt" strTextFind = "myVariableName" strTextReplace = "myChangedVariableName" ActiveWorkbook.VBProject.VBComponents("Module1").Export strSource Close Open strSource For Input As #1 Do Until EOF(1) Line Input #1, strText If InStr(strText, strTextFind) Then strText = Replace(strText, strTextFind, strTextReplace) iCounter = iCounter + 1 ReDim Preserve arr(1 To iCounter) arr(iCounter) = strText Loop Close Open strTarget For Output As #1 For iCounter = 1 To UBound(arr) Print #1, arr(iCounter) Next iCounter Close On Error Resume Next Shell "notepad " & strTarget, vbMaximizedFocus Err.Clear End Sub