EXPIRED

Find and Replace on multiple Excel files

Hi,

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:

Sub changeout()
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")
Do
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
Next
wb.Close True
fName = Dir
Loop While fName <> ""
Hndlr:
If Err.Number <> 0 Then
MsgBox "No Excel Files in Directory"
End If
End Sub

6 Comments

Your want to be on StackExchange for this. They'll help.

What error are you getting?

I would change your loop to:

Do While fName <>""
(etc)
Loop

you can get rid of lot of the error handling that way.

Are your replace params right? You could try:
sh.UsedRange.Replace "C:\Users\ihoaa\Desktop\External Data\Batch Report", "", xlPart
...that seems to be the method most use for this.
Edited by: "Tony68k" 11th Dec 2014

Original Poster

Thanks for the replies guys.

The code currently is:

Sub changeout()
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 & "*.xls")
Do
On Error GoTo Hndlr:
Set wb = Workbooks.Open(fName)
On Error GoTo 0
For Each sh In wb
sh.UsedRange.Replace "[Template Report.xlsm]", "", xlWhole, xlByRows, False
Next
wb.Close True
fName = Dir
Loop While fName <> ""
Hndlr:
If Err.Number <> 0 Then
MsgBox "No Excel Files in Directory"
End If
End Sub


It runs up until:
For Each sh In wb

Then I get an error message saying
'Run time error 438'
Object doesn't support this property or method'

Excel files have the text viewable as text so it doesn't have to be done using VB.
There are a lot of programs which will find and replace text for you such as grepWin

Original Poster

YAY!

All sorted now. Thanks for the replies people, mucho appreciated.

Sub changeout()
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 & "*.xls")
Do
On Error GoTo Hndlr:
Set wb = Workbooks.Open(fName)
On Error GoTo 0
For Each sh In wb.Worksheets
sh.Cells.Replace What:="[Template Report.xlsm]", Replacement:="", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next
wb.Close True
fName = Dir
Loop While fName <> ""
Hndlr:
If Err.Number <> 0 Then
MsgBox "No Excel Files in Directory"
End If
End Sub

Try:
For Each sh in wb.WorkSheets

Looks like sh isn't getting set to an object.

Post a comment
Avatar
@
    Text
    Top Discussions
    1. Does Tesco call you after a delivery?11
    2. [Solved] How to use openreach modem with Asus RT-AC68U?524
    3. Kia Rio 1.0 T-GDI (petrol) vs Rio 1.4 CRDI (diesel)11
    4. Bed pillow recommendations44

    See more discussions