Change vba code with vba code

[Maurizio]

Hi all,

into "Each vbp In ThisWorkbook.VBProject.VBComponents" how can I, with VBA code, change this code's string:

from:
Public Const CTBOld = "Contab03OLD.xls"
Public Const CTB = "Contab03.xls"

to:
Public Const CTBOld = "Contab04OLD.xls"
Public Const CTB = "Contab04.xls"


For example:

For Each vbp In ThisWorkbook.VBProject.VBComponents
With vbp
With vbp.CodeModule

????????????????????????
?????????????????????????
?????????????????????????

End With
End With
Next vbp

End Sub

 

[acw]

Hi

Try
Sub aaa()
For i = 1 To Application.VBE.CodePanes.Count
Application.VBE.CodePanes(i).CodeModule.ReplaceLine 1, "public const CTBold = ""contab04old.xls"""
Application.VBE.CodePanes(i).CodeModule.ReplaceLine 2, "public const CTB = ""contab04.xls"""
Next i

End Sub

Tony

 

[acw]

Hi

The site
http://www.cpearson.com/excel/vbe.htm
has some examples of how to use code to modify code.

Tony

 

[Maurizio]

Thanks Tony,
but your solution gine me same problems with xls97' reference.

This runs OK:
'http://xlbysteph.free.fr/aideinformatique/news/vbachangelines.txt

Sub ChangeLineInSubs()

Dim myBook As Workbook
Dim i
Dim j

For Each myBook In Application.Workbooks
For i = 1 To myBook.VBProject.VBComponents.Count
For j = 1 To myBook.VBProject.VBComponents(i).CodeModule.CountOfLines
If myBook.VBProject.VBComponents(i).CodeModule.Lines(j, 1) = "Public Const CTBold = ""Contab03old.xls""" Then
myBook.VBProject.VBComponents(i).CodeModule.DeleteLines j
myBook.VBProject.VBComponents(i).CodeModule.InsertLines j, "public const CTBold = ""Contab04old.xls"""
End If
Next j
Next i
Next myBook
End Sub