CODEWITHSUNDEEP
excelvba
My Display Name
My Display Name

Reputation: 27

Excel Macro to replace string in multiple excel files

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

Upvotes: 0

Views: 1162

Answers (2)

Ultra Junkie
Ultra Junkie

Reputation: 176

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.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166895

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

Upvotes: 2

Related Questions