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.
 
Hi there,

This should work for you. Change the constants at the top to meet your needs...

Code:
Option Explicit

Const sPath As String = "C:\Users\Z\Desktop\testt\"
Const sFind As String = "email@find.com"
Const sRepl As String = "email@replace.com"
    
Sub CopySameSheetFrmWbs()
    Dim wbOpen As Workbook, ws As Worksheet
    Dim sExt As String, bWbOpen As Boolean, sName As String
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    On Error Resume Next
    ChDir sPath
    sExt = Dir("*.xls")
    Do While sExt <> ""
        sName = Right(sPath & sExt, Len(sPath & sExt) - InStrRev(sPath & sExt, "\"))
        Set wbOpen = Workbooks(sName)
        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
 
Upvote 0
Zack, Thank you so much! Um, I'm new to macros, so I guessed to paste the code (with the modifications you suggested at top) into/ as a new MODULE (macro I believe). But I'm kinda stuck at what to do now, because I tried to open up other files and do (I think?) a "Run", but couldn't find the macro that I pasted into that first file. Thanks for providing maybe some guiding steps. Wow. Your help is truly appreciated.

Thank you.
 
Upvote 0
Yes, the code goes into a Standard Module. Go to your Visual Basic Editor (Alt + F11), make sure you have your Project Explorer open (Ctrl + R), find your workbook you want to put the code in and select that project (aka file/workbook). Now click on the Insert menu, select Module. A new code pane will appear. Paste the code in there. Make sure it pastes over everything. You don't want to have two identical Option statements (you can't) in the same module.

Post back if you need any more help. :)
 
Upvote 0
hey Zack, thanks. Funny, but everything you said is precisely what I was up to do on my own before last posting...but that's precisely where I hit my road block being new to macros. So I got it in that one file (and was even able to do an F5 run and watch it replace the email address "in real time" within that one file) "BUT" I don't know how to get it to go thru all the files within that folder automatically to recognize that macro (without me having to manually paste the code into each file and run each file individually which I'm sure is not what you want me to do since it would defeat the purpose). So, thanks for showing me the easier, proper way. Kind regards for everything. Thank you.
 
Upvote 0
Right, no need to do that. Did you change the three lines at the top, to include changing your entire path (leave the backslash on the end), and both email addresses? What is your OS and Excel version?
 
Upvote 0
Yes I did exactly those things. I'm 2003 & XP.
Am I supposed to be running it once from within the file its in and without the code in the other files, right bud? I'm using a sample of maybe 7 files within that one folder that's in the path ending in \

Thanks.
 
Upvote 0
Yeah, it works for me. They're all Excel files, right? Open or closed (even though it shouldn't make a difference)? Step through the code with F8 to see what's going on.
 
Upvote 0
When I do na Debug > F8 (step into), it just highlights the line in the code:

Sub CopySameSheetFrmWbs()

Yeah, all the files in that folder are .xls

When I do a Run and choose that particular macro (CopySameSheetFrmWbs), the explorer window shakes and scroll moves a bit (I guess it's running thru the files), but not really sure because none of the email addresses get replaced. Thanks for hangin' in there Zack to help me find the soln.

Ben
 
Upvote 0
When I do na Debug > F8 (step into), it just highlights the line in the code:

If you keep pressing F8, it will execute the code line-by-line.

Might I suggest reducing VBIDE to about 1/3 screen and the wb view to the other 2/3rds. Then step thru it and you can see it open teh workbooks.

After it executes ws.Cells.Replace sFind, sRepl the first time for a given workbook, see if the address has been replaced in the first sheet.
 
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