Find and Replace on multiple Excel files - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit
Expired

Find and Replace on multiple Excel files

£0.00 @
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 with…
allsa001 Avatar
2y, 3m agoPosted 2 years, 3 months ago
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
allsa001 Avatar
2y, 3m agoPosted 2 years, 3 months ago
Options

All Responses

(6) Jump to unreadPost an answer
Responses/page:
#1
Your want to be on StackExchange for this. They'll help.

What error are you getting?
#2
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 on Dec 11, 2014 16:18: Added a bit
#3
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'
#4
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
#5
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
#6
Try:
For Each sh in wb.WorkSheets

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

Post an Answer

You don't need an account to leave a response. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!