Macro to "Find - Replace" in Background

[raj08536]

Hi Experts,

Here is an existing Macro to find and replace links in 100 worksheets.

Sub Replace()

Fnd = Sheets("000.Current month").Cells(10, 2).Value
Rplace = Sheets("000.Current month").Cells(11, 2).Value

For Each sht In Worksheets
If sht.Name <> "000.Current month" Then
sht.Activate
Cells.Replace What:=Fnd, Replacement:=Rplace, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End If
Next sht

End Sub

Understandably It takes very long and therefore, hold me to use excel. Can I change macro to work in the background while I am doing other assignments in Excel?

Thanks

Raj

 

[Dan_W]

You could also just start another instance of Excel - have one running a macro over the workbook, and then work on the second.

 

[GWteB]

In addition to Dan_W's suggestion, within time consuming procedures it is recommended (in connection with the architecture and behaviour of a multi-tasking OS like the Windows OS) to let the OS know at regular intervals that your VBA is still running by yielding to the OS using the DoEvents statement. One wants to prevent Windows (because of the suspicion of an infinite loop) from decreasing the priority of the running process (i.e. your macro) when assigning processor time.

Sub Replace()

    Fnd = Sheets("000.Current month").Cells(10, 2).Value
    Rplace = Sheets("000.Current month").Cells(11, 2).Value
   
    For Each sht In Worksheets
        If sht.Name <> "000.Current month" Then
            sht.Activate
            Cells.Replace What:=Fnd, Replacement:=Rplace, LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                          ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        End If
        Dim i As Long
        i = i + 1
        If i Mod 2 = 0 Then DoEvents
    Next sht
End Sub

 

[raj08536]

Ok. I got it.

I want to create a file and store this macro and call this file as "FindReplace.xlsm" path "C;\\testing"

I have multiple files in the same path or can be in other directory. How can I call Replace Macro from "FindReplace.xlsm" in each file. I think in each file I have to create another macro to open "FindReplace.xlsm" and call replace macro

How do I write the VBA code in each file?

 

[GWteB]

If you're in the need of processing multiple files, it seems to be more efficient to make just one macro procedure (in one workbook) responsible for that, rather then duplicating your code accross all the workbooks to be processed. Do you agree?

 

[raj08536]

If you're in the need of processing multiple files, it seems to be more efficient to make just one macro procedure (in one workbook) responsible for that, rather then duplicating your code accross all the workbooks to be processed. Do you agree?

so how do you suggest?

 

[GWteB]

You wrote that you intended to call the FindReplace macro from multiple workbooks. This implies that each workbook from which you wanted to call that macro consists of any number of worksheets, but that no worksheet has the name "000.Current month". Am I right?

 

[GWteB]

The code below is not as efficient as it could be (and prone to errors), but it illustrates what I mean. The ProcessWorkbooks procedure is the main procedure and calls the RReplace procedure several times, each time for a different workbook.

Sub ProcessWorkbooks()

    Dim oWb As Workbook
    Dim Fnd As Variant
    Dim Rplace As Variant

    With ThisWorkbook.Sheets("000.Current month")
        Fnd = .Cells(10, 2).Value
        Rplace = .Cells(11, 2).Value
    End With

    Set oWb = Workbooks.Open("C:\folderA\xlfile01.xlsx")
    Replace oWb, Fnd, Rplace
    oWb.Close
    
    Set oWb = Workbooks.Open("C:\folderB\xlfile02.xlsx")
    Replace oWb, Fnd, Rplace
    oWb.Close

    Set oWb = Workbooks.Open("C:\folderC\xlfile03.xlsx")
    Replace oWb, Fnd, Rplace
    oWb.Close

End Sub


Sub RReplace(ByVal argWb As Workbook, ByVal argFind As Variant, ByVal argReplace As Variant)
    Dim sht As Worksheet
    Dim i as Long

    For Each sht In argWb.Worksheets
        Cells.Replace What:=argFind, Replacement:=argReplace, LookAt:=xlPart, _
                      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                      ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        i = i + 1
        If i Mod 2 = 0 Then DoEvents
    Next sht
End Sub