Custom Find & Replace

[ravikiran]

Hi Gurus,

I need some help with Find & replace functionality. Just to confirm, I tried many options without any luck. I am able to automate the whole process except for this functionality, forcing me to do the job manually. Requirement below:

1. Mapping worksheet contains a mapping table containing "Old Table Names" and "New Table Names"
2. Queries worksheet contains a list of SQL Queries which needs to be amended with the new table names.
3. Requirement - I need to loop through the Mapping table and do a find and replace in the Queries.
4. Complexity - I need to replace the table name only in the highlighted (bold) parts below:


SELECT dboOrders.OrderID, dboCustomers.CustomerName, dboOrders.OrderDate
FROM dboOrders
INNER JOIN dboCustomers ON dboOrders.CustomerID=dboCustomers.CustomerID;


When I use the normal Find/Replace, all the instances of dboOrders are being replaced, which doesn't serve the purpose for me.

Is there any way we can iterate through each word within the query and do a replace using VBA?

Any help with this macro is very appreciated.

Cheers,
Ravi.

 

[Luke M]

Understanding the small example, it looks like you don't want to touch any of the items that have a period following the table name. In that case, for each word, I'd first do a find an replace for
Find: myWord & "."
Replace: "ZZZ."

Then, go through and do a find and replace for
Find: myWord
Replace: newWord

Finally, go back and undo step 1
Find: "ZZZ."
Replace: myWord & "."

 

[ravikiran]

Hi Luke,

Thanks, I tried this option but this doesn't work for me. I was able to get the following VBA code to do the job as required (though with another issue).

Sub FindReplace()
'
'
Dim cel As Range, celQ As Range
Dim Find As String, Replace As String
Dim tblName As String
Dim lrow As Integer

    With Sheet9
        For Each cel In .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
            Find = cel.Value
            Replace = cel.Offset(0, 2).Value
lrow = 1
            With Sheet1
                Do Until lrow = 10
                    Set celQ = .Range("B" & lrow)
                    Call LoopThroughString(celQ.Value, Find, Replace)
                    lrow = lrow + 1
                Loop
            End With
        Next
    End With

End Sub

Sub LoopThroughString(ByVal LookInHere As String, ByVal Find As String, ByVal Replace As String)
'
'
Dim Counter As Integer
Dim SplitCatcher As Variant
Dim str As String

Debug.Print LookInHere

    'Use your own text here
    SplitCatcher = Split(LookInHere, " ")

    For Counter = 0 To UBound(SplitCatcher)
        If SplitCatcher(Counter) = Find Then
            str = str & " " & Replace
        Else
            str = str & " " & SplitCatcher(Counter)
        End If
    Next

    SplitCatcher = Split(str, char(10))
  

Debug.Print Trim(str)

End Sub

One small issue: I was able to split the query by "SPACE", though the queries have LineFeeds as well, which is not allowing the replace to complete successfully.

SELECT dboOrders.OrderID, dboCustomers.CustomerName, dboOrders.OrderDate
FROM dboOrders
INNER JOIN dboCustomers ON dboOrders.CustomerID=dboCustomers.CustomerID;

The first replace isn't working as it trails by LineFeed instead of a SPACE.

Any Idea how this can be fixed?

Cheers,
Ravi.