Find And Replace in Multi excel files with multi worksheets

kalagas

New Member
Joined
Feb 15, 2019
Messages
25
Hello to anyone.
I'm trying to find a solution (a vba) that open all excel files inside a folder, then search IN ALL WORKSHEETS of each excel file for "40;" and replace it with "48;" , then save and close files.

I found the following vba in microsoft support website, but it doesn't work for me. It doesn't change anything.

Code:
Sub ReplaceInFolder()    Dim strPath As String
    Dim strFile As String
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim strFind As String
    Dim strReplace As String
    strFind = InputBox("Enter text to find")
    If strFind = "" Then
        MsgBox "No find text specified!", vbExclamation
        Exit Sub
    End If
    strReplace = InputBox("Enter replacement text")
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then
            strPath = .SelectedItems(1)
        Else
            MsgBox "No folder selected!", vbExclamation
            Exit Sub
        End If
    End With
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    Application.ScreenUpdating = False
    strFile = Dir(strPath & "*.xls*")
    Do While strFile <> ""
        Set wbk = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)
        For Each wsh In wbk.Worksheets
            wsh.Cells.Replace What:=strFind, Replacement:=strReplace, _
                LookAt:=xlWhole, MatchCase:=False
        Next wsh
        wbk.Close SaveChanges:=True
        strFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

The "40;" i want to change is part IF(ISNA(VLOOKUP.....)) formula.

If i open manually the files and use the excel default Find & Replace function it works like a charm. But as you understand it will take me a lot of time to make this change manually by opening one by one the 100+ files i have to change.

P.S. All excel files and their sheets are not protected.

Thanks In Advance for your time.
 
Last edited:
That works for me.
Try stepping through the code using F8 and when you get to the line that doing the replacing, hover the cursor over strFind & strReplace to check that they still fold the correct values.
Also at the same time you can check that it is actually opening workbooks.
Is it possible to be effected from latest windows 10 update KB4512508 that has issues with macros using Visual Basic for Applications (VBA)?
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Not that I'm aware of, your code works for me with that update.
Did you try what I suggested in post#10?
If so what were the results?
 
Upvote 0
When you hovered over the variables, did they still have the correct value?
Also where the workbooks being opened?
 
Upvote 0
When you hovered over the variables, did they still have the correct value?
Also where the workbooks being opened?
Yes when i hovered over strFind it showed "40;" over strReplace it showed "48;" and all files were being opened.
 
Upvote 0
In that case I'm afraid I have no idea why it's not working.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top