How to Find & Replace a particular string (an email address) found within all excel files within one folder

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
Hi. I'm wondering if anyone can provide a quick way to do a find & replace one email address that's in a few hundred excel files that are all within a single folder. Thanks very much for your solution and your time.
 
GTO, thanks for your help:

You wrote ["If you keep pressing F8, it will execute the code line-by-line."],

but I can only get one F8 out of it. In other words, it just works once highlighting that one line called:

Sub CopySameSheetFrmWbs()

Then if I try more F8s, nothing happens; just stays.

Also, i apologize, but everything after that ("Might...sheet"), I didn't understand at all. I'm a beginner. Thanks.
 
Upvote 0
Okay, if you press F8 repeatedly and its not advancing, that is strange.

What year Excel are you in?

Lets take a step back. When you are looking at the code in the code window (AKA VBIDE) what exactly is in the titlebar. That is the bar at the very top.
 
Upvote 0
Excel 2003.

Title bar says "Microsoft Visual Basic (then my folder path & filename).xls-[Module1 (Code)]"

Thank you.
 
Upvote 0
Well, I'm not sure why its showing the path, but everything else looks right for you to be in a standard module.

I have to hit the sack for a quick nap (currently on nights, yech) but if you get a chance to do this quick... Temporarily change Zack's code to:

Rich (BB code):
Sub CopySameSheetFrmWbs()
    Dim wbOpen As Workbook, ws As Worksheet
    Dim sExt As String, bWbOpen As Boolean, sName As String
    Application.EnableEvents = True ' False
    Application.ScreenUpdating = True ' False
    Application.Calculation = xlCalculationAutomatic ' xlCalculationManual
'    On Error Resume Next
    ChDir sPath
    sExt = Dir("*.xls")
    Do While sExt <> ""
        sName = Right(sPath & sExt, Len(sPath & sExt) - InStrRev(sPath & sExt, "\"))
        On Error Resume Next
        Set wbOpen = Workbooks(sName)
        On Error GoTo 0
        bWbOpen = True
        If wbOpen Is Nothing Then
            Set wbOpen = Workbooks.Open(sPath & sExt)
            bWbOpen = False
        End If
        For Each ws In wbOpen.Worksheets
            ws.Cells.Replace sFind, sRepl
        Next ws
        If bWbOpen = False Then
            wbOpen.Close SaveChanges:=True
        Else
            If wbOpen.Saved = False Then wbOpen.Save
        End If
        Set wbOpen = Nothing
        sExt = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Now ensure that you place the cursor in this line:
Rich (BB code):
    Application.Calculation = xlCalculationAutomatic ' xlCalclationManual
...and press the F9 key. Does it change the line to a highlighted maroon? If yes, good - we have placed a BreakPoint.

Now if it did turn maroon, press the F5 key. It should now highlight that line in yellow, as it 'Breaks' or pauses the running code at the breakpoint.

IF it did change to yellow, now press F8 key and see if it steps.

Elsewise I cannot think of anything other than a busted F8 key?
 
Upvote 0
awesome. i'll give it a try and follow up w/ you as soon as i have something for you. gnyt and kind regards. ben
 
Upvote 0
Ok, I got a compile error:

"Only comments may appear after End Sub, End Function, or End Property"

which I think is bad syntax for what they really mean:

"Comments may only come after End Sub, End Function, or End Property"

Thanks.
 
Upvote 0
You may have picked up garbage when copying from the thread. If there is anything red or anything after End Sub, ditch it.
 
Upvote 0
No, but here's some more info: When I look at what happens during that Compile Error I just mentioned, the following word in ur revised macros highights (well, is selected):

wbOpen of

the 1st line after the FIRST Endsub, or wbOpen within the 9th line from the bottom gets highlighted in blue as part of that error.

Hey, I thought you were going to bed? Man, ur just like me. That's a bad habit. Get some sleep.
 
Upvote 0
No, but here's some more info: When I look at what happens during that Compile Error I just mentioned, the following word in ur revised macros highights (well, is selected):

wbOpen of

I am lost, I've read the code twice at least and there's no line with "wbOpen of" in it.

the 1st line after the FIRST Endsub, or wbOpen within the 9th line from the bottom gets highlighted in blue as part of that error.

I am afraid I am totally befuddled. There is only one End Sub and the ninth line from the bottom is :
Rich (BB code):
If wbOpen.Saved = False Then wbOpen.Save

Please use [code=rich]...code goes here[/code]tags to post the code neatly, and post exactly what you have, from the top of the module to the bottom.
 
Upvote 0
With your cursor in the module, press Ctrl + A, then paste it in a post here, so we can see everything in the module. You shouldn't be having a compile error unless you altered the code somehow, otherwise it wouldn't have compiled before the 'on error' statement was in there.
 
Upvote 0

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