RE: Automatic replace of VBA code for multiple files
- From: jiewan@xxxxxxxxxxxxxxxxxxxx ("Jie Wang [MSFT]")
- Date: Mon, 26 Oct 2009 09:03:47 GMT
Hi Dan,
Like Barb said, we can use VBE OM to do what you want.
One thing to notice is, to protect the users from Macro viruses, the
programmatic access to VBA is disabled by default. You need to enable it
before using the following code to replace a specific string with another
one in one or more Excel workbooks. For more information, please refer to
this KB aritcle: http://support.microsoft.com/kb/282830.
Here is a piece of code I wrote to show how to do what you want, since you
didn't say your Excel version, I'm using 2007 in the sample, but only
slight changes are needed to make to work with 2003. You also need to add a
"Microsoft Visual Basic for Applications Extensibility" reference to your
VBA project to run the demo.
' *** Start code ***
Public Sub DoIt()
On Error GoTo Err
Dim filename As String
Dim path As String
Dim wb As Workbook
' Start loop through all the macro-enabled workbooks in the specified
directory.
path = "C:\Target Path\" ' your target path here
filename = Dir(path & "*.xlsm")
Do While filename <> ""
Set wb = Application.Workbooks.Open(path & filename)
ReplaceStringInVBA wb, "This string", "That string"
wb.Save
wb.Close
filename = Dir
Loop
Exit Sub
Err:
MsgBox Err.Description, vbCritical Or vbOKOnly
wb.Close False
End Sub
Public Sub ReplaceStringInVBA(ByVal wb As Workbook, ByVal searchFor As
String, ByVal replaceWith As String)
Dim i As Long
' Loop through all the project items
For i = 1 To wb.VBProject.VBComponents.Count
Dim cm As CodeModule
Dim lines As Long
Dim l As Long
Set cm = wb.VBProject.VBComponents.Item(i).CodeModule
lines = cm.CountOfLines
' Loop through all the lines in the code module
For l = 1 To lines
Dim ln As String
ln = cm.lines(l, 1)
' If we found a match in the line, replace it
If InStr(1, ln, searchFor, vbTextCompare) > 0 Then
cm.ReplaceLine l, Replace(ln, searchFor, replaceWith, , ,
vbTextCompare)
End If
Next
Next
End Sub
' *** End code ***
Please let me know how it goes.
Thanks,
Jie Wang
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@xxxxxxxxxxxxxx
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
.
- Follow-Ups:
- References:
- Prev by Date: Floating, unprintable objects in Excel???
- Next by Date: Lookup second duplicate value location.
- Previous by thread: RE: Automatic replace of VBA code for multiple files
- Next by thread: RE: Automatic replace of VBA code for multiple files
- Index(es):
Relevant Pages
|
