Find And Replace in Multi excel files with multi worksheets

[kalagas]

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.

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.

 

[kalagas]

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)?

 

[Fluff]

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?

 

[kalagas]

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?

Yes i tried it but nothing happened. It doesn't change the 40; to 48;

 

[Fluff]

When you hovered over the variables, did they still have the correct value?
Also where the workbooks being opened?

 

[kalagas]

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.

 

[Fluff]

In that case I'm afraid I have no idea why it's not working.