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

[thankyou]

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.

 

[thankyou]

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.

 

[GTO]

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.

 

[thankyou]

Excel 2003.

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

Thank you.

 

[GTO]

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:

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:

    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?

 

[thankyou]

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

 

[thankyou]

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.

 

[GTO]

You may have picked up garbage when copying from the thread. If there is anything red or anything after End Sub, ditch it.

 

[thankyou]

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.

 

[GTO]

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 :

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.

 

[Zack Barresse]

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.