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
Hi Tom
This article may be more useful if you added an explanation as to WHY one would want to do this (and with exporting a complete module), given that VBE has built-in Find & Replace functionality. At this stage I cannot see why I would bother, but am happy to be enlightened.
Thanks for following my Excel blog, Colin.
I’ve been contributing to Excel forum newsgroups since 1999. A lot of my ideas for solution examples that I post on my blog come from not just one, but a few questions of a similar nature that I’ve seen asked over the years that indicate to me that the topic has interest to this niche or that niche in Excel. Sometimes I’ve been surprised at how a popular a topic would be that I never thought would resonate.
In this case, I got the idea for this example over the years from several developers of large development projects that were sharing code amongst themselves as a way to commiserate about optimal VBA and API approaches to the overall project, and how those proposed examples might influence (usually negatively was the understandable concern) the portions of the program that other programmers were working on. It makes sense in some development cases, instead of having multiple workbook revisions flying back and forth by email or created and piling up on a shared drive. Other people involved with the project can grab proposed code from a shared drive text file; they can test that proposed code locally without bothering other developers involved with the project.
Another reason is for visual vetting to say, hey, what do you think about this syntax, I know it will work with the project but is there a more efficient way you’d recommend. People familiar enough with VBA can quickly eyeball a structure and see the good, bad, and ugly of it, without needing to plug it in or see the entire project.
Finally, this example I posted need not involve a large project. Speaking for myself, I’ve been programming Excel since 1994, and like many others, I’ve amassed a library of personal favorites: tried and true, battle tested code examples. I want to keep a record of that collection without managing the overhead weight of their associated thousands of workbooks only for the sake of viewing my code in their original modules.
I hope this answers your question. Thanks again for visiting my page.
-Tom
Code to export along with Find & Replace is really a good idea,.
I’m looking for Data Analysis projects if u can plzz post something different @professional level, ☺