find and replace code through another code

aadarsh · Jun 16th 2005

Hi

Dont think can be done, but no harm trying

Is there any way i can write a code, which searches through the code of another file, finds a keyword, and replaces by another

Please note:I want to search the code not the excel file.

Aadarsh

**iwrk4dedpr** · Jun 16th 2005

Re: find and replace code through another code

aadarsh,

Yes. Go to the help VBA help and search for help on the topic of VBE. This will show you how to access the code for a workbook.

aadarsh · Jun 16th 2005

Re: find and replace code through another code

Hi

Not to be rude, but i know how to access VBE...

the thing is in code if say i have

Code

a="temp"

Say I want to replace 'temp' with another word say 'ace', i would normally go to find and replace in VBA and do it...

Now i want to find and replace this section of cod through another code, rather than manually going to find and replace

Aadarsh

**iwrk4dedpr** · Jun 16th 2005

Re: find and replace code through another code

aadarsh,

Ok so by using a macro you can access the VBE envrionment of another workbook. That's half the battle.

Here's some code that I wrote 3 years ago for exporting code from a workbook to a worksheet. You should be able to modify it to do a string replace

Code

'   Step 2 : Export Each line of code to a worksheet
    For Each ref In Workbooks(wkBookName).VBProject.VBComponents
        If ref.codemodule.CountOfLines > 3 Then
        ' For Each Module add New Sheet / change sheet name = Module Name
            Call AddNewSheetForVBCode
            Debug.Print ref.Name
            ActiveSheet.Name = Left(ref.Name, 31)
            Range("B1").Select
        End If
        If ref.codemodule.CountOfLines > 3 Then
            ActiveCell.Offset(1, 0).Select
            For c = 1 To ref.codemodule.CountOfLines
                Debug.Print ref.codemodule.Lines(c, 1)
                ActiveCell = " " & ref.codemodule.Lines(c, 1)
                Call HighlightLine(CStr(ref.codemodule.Lines(c, 1)))
                ActiveCell.Offset(0, -1) = ActiveCell.Row
                ActiveCell.EntireRow.WrapText = True
                ActiveCell.Offset(0, -1).Borders(xlEdgeBottom).Weight = xlThin
                ActiveCell.Offset(1, 0).Select
            Next c
            ActiveCell.Offset(1, 0).Select
        End If
    ' Place file info in Cell A1 of active sheet
        If ActiveSheet.Name <> "Start Here" Then
            Rows("2:2").Insert Shift:=xlDown
            strFilePath = Workbooks(wkBookName).path
            strFileName = Workbooks(wkBookName).Name
            Range("A1") = strFilePath & "\" & strFileName
        End If
    Next ref

Display More

There was more but this was the meat of the routine.

aadarsh · Jun 16th 2005

Re: find and replace code through another code

Thanx

Am working on it...will put the code up for reference when i am done

Aadarsh

aadarsh · Jun 16th 2005

Find and replace code through another code - Solution posted

Hi Barry here is a module i created for making the change

Code

Function replaceincode(wbook As workbook, sourcestring As String, replacestring As String)


Dim s As Variant
Dim t As Variant


For Each ref In wbook.VBProject.VBComponents
    Count = ref.CodeModule.countoflines
    If Count > 0 Then
        b = ref.CodeModule.Lines(1, Count)
        s = Split(b, "" & vbNewLine)
        For i = 0 To UBound(s)
            If InStr(1, s(i), sourcestring,vbTextCompare) Then
                t = Split(s(i), sourcestring,vbTextCompare)
                outcode = t(0)
                For j = 1 To UBound(t)
                    outcode = outcode + replacestring + t(j)
                Next j
            a = ref.CodeModule.ReplaceLine(i, outcode)
            End If
       
        Next i
    End If
Next


End Function

Display More

find and replace code through another code

Participate now!

Share