Excel Macro to replace string in multiple excel files

Question

I am trying to create a VB Macro to replace a string found in multiple excel files in a directory. My code is below but it is not working and I am not sure what I need to do to fix it.

Any suggestions ?

Sub ReplaceStringInExcelFiles()

Dim MyFile As String
Dim FilePath As String
Dim orig As String
Dim news As String

orig = "cow"
news = "dog"

FilePath = "C:\myDir\"
MyFile = Dir(FilePath)


Do While Len(MyFile) > 0

Workbooks.Open (FilePath & MyFile)
For q = 1 To Application.Worksheets.Count
Worksheets(q).Activate
Sheets("Sheet1").Cells.Replace what:=Original_String, Replacement:=New_Replacement_String, lookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next q
ActiveWorkbook.Save
ActiveWorkbook.Close
MyFile = Dir
Loop


End Sub

"not working" is not a useful description of what happens when you run your code. — Tim Williams
– Tim Williams, Commented Jun 15, 2020 at 16:00
There is no error when I try to run it from Excel. But none of the files open up and get updated with the string. No execution starts — My Display Name
– My Display Name, Commented Jun 15, 2020 at 16:07
I am new to VB. It took me several hours to create the code and I am not sure if it is correct . This is the reason for my posting to the website — My Display Name
– My Display Name, Commented Jun 15, 2020 at 16:08

Tim Williams · Accepted Answer · 2020-06-15 16:32:51Z

2

Try this:

Sub ReplaceStringInExcelFiles()

    Dim MyFile As String
    Dim FilePath As String
    Dim orig As String
    Dim news As String
    Dim wb As Workbook, ws As Worksheet

    orig = "cow"
    news = "dog"

    FilePath = "C:\myDir\"
    MyFile = Dir(FilePath & "*.xls*")


    Do While Len(MyFile) > 0
        Set wb = Workbooks.Open(FilePath & MyFile) '<< assign the workbook to wb
        'Loop over the worksheets
        'Note: no need to activate/select
        For Each ws In wb.Worksheets
            ws.UsedRange.Cells.Replace what:=orig, _
                            Replacement:=news, _
                            lookAt:=xlPart, SearchOrder:=xlByRows, _
                            MatchCase:=False
        Next ws
        ActiveWorkbook.Close savechanges:=True
        MyFile = Dir
    Loop

End Sub

answered Jun 15, 2020 at 16:32

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ultra Junkie · Accepted Answer · 2020-06-15 16:57:10Z

0

Excel is known for needing clear references. Always make sure that all your objects have a path to the application object as root.

Also it is a good habit to start with

 option explicit

which helps finding such potential bugs.

Tim's code looks pretty good and should work.

answered Jun 15, 2020 at 16:57

Ultra Junkie

1747 bronze badges

3 Comments

Tim Williams Over a year ago

Please don't post comments as answers.

Ultra Junkie Over a year ago

I know. Mea culpa, but I am also new here and haven't yet found out how to use such simple things like a CRLF in the comment field. The help was not very helpful so far (or I overread the solution). Any hint is more than welcome...

Tim Williams Over a year ago

You can't put a newline in a comment as far as I know. If you need to put code in a comment you can put backticks around it like Option Explicit

Collectives™ on Stack Overflow

Excel Macro to replace string in multiple excel files

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related