Code to edit code

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Is it possible to write code to open an excel workbook,
access the code (that is password protected), perform a
Find/Replace All, and close the workbook, saving changes?
 
It's possible to open an Excel workbook and edit the code - but, AFAIK, only
if the code is not protected.

I don't think there is anyway programatically to unprotect the code, even if
you know the password.

Tim
 
Sam,

Assuming your code is unprotected, you could use this code:

I've added a second condition so that it doesn't modify it's own procedure
(Not .ProcOfLine(i, vbext_pk_Proc) = "testit" )

Sub testit()
Dim strFind As String, strReplace As String, vbc As VBComponent, i As
Long, j As Long

strFind = "drink me"
strReplace = "eat me"

For Each vbc In ThisWorkbook.VBProject.VBComponents
With vbc.CodeModule
For i = 1 To .CountOfLines
j = InStr(1, .Lines(i, 1), strFind, vbTextCompare)
If j > 0 And Not .ProcOfLine(i, vbext_pk_Proc) = "testit"
Then _
.ReplaceLine i, Replace(.Lines(i, 1), strFind,
strReplace, , , vbTextCompare)
Next
End With
Next
End Sub

Cheers
 
Back
Top