I was wondering if anyone knew of a way using Visual Basic to do a find and replace on all the excel within a folder.
I basically want to remove all instances of a string of a text from within all the excel files within a directory.
The code I am using at the moment is as follows, but it returns an error message:
Dim sh As Worksheet, fPath As String, fName As String, wb As Workbook
fPath = "C:\Users\ihoaa\Desktop\External Data\Batch Report\"
fName = Dir(fPath & "*.xlsx")
On Error GoTo Hndlr:
Set wb = Workbooks.Open(fName)
On Error GoTo 0
For Each sh In wb
sh.UsedRange.Replace "C:\Users\ihoaa\Desktop\External Data\Batch Report", "", xlWhole, xlByRows, False
fName = Dir
Loop While fName <> ""
If Err.Number <> 0 Then
MsgBox "No Excel Files in Directory"