Change VBA encoding with VBA encoding

Question

I need to change the constant referencing the web address. Our ADI templates have a new server path, and it would be easier to update the code than to download all new templates. How can I update below:

Const SERVLET_PATH = "http://webaddress.com" 

to

 Const SERVLET_PATH = "http://webaddress1.com" 

Is it possible?

Answer 1

First, you need to add a link to the Microsoft Visual Basic for Applications Extensibility 5.3 library.

Click Tools → Links → Microsoft Visual Basic for Application Extensibility 5.3

Then you need to open the book containing the module that you want to update. This means that you cannot make this update while users open the book.

 Workbook.Open filePath 

Then you will need to skip each code module in the book, looking for the constant you want to change.

 Sub replaceConstant() Dim project As VBIDE.VBProject For Each project In Application.VBE.VBProjects Dim codeMod As VBIDE.CodeModule Dim component As VBIDE.VBComponent For Each component In project.VBComponents If component.Name <> "TheVeryUniqueNameOfTheCodeModuleWhereThisCodeResides" Then Set codeMod = component.CodeModule Dim startline As Long startline = 1 'find takes startline in byref and uses it as an output parameter. codeMod.Find Target:="Const SERVLET_PATH = ""http://webaddress.com""", _ startline:=startline, startcolumn:=1, endline:=codeMod.CountOfLines, endcolumn:=1 codeMod.ReplaceLine startline, "Const SERVLET_PATH = ""http://webaddress1.com""" End If Next component Next project End Sub 

The above code works because:

The Find method accepts ByRef Long parameters. As you enter, these options determine the range of rows and column to search. At the output, these values ​​indicate the text found.

CPearson.com - VBA Editor Programming

Of course, you need to do this for every book you want to change. This may take some time.

Additional resources:

• CodeModule Object
• Find method
• ReplaceLine Method