0

I am hoping that @TimWilliams sees this. About 2 years ago he replied to a question regarding changing a string in multiple spreadsheets with the below VBA. I am trying to do the same, but not change a string in worksheets, I am trying to change a string in the VBA modules of the different spreadsheets (to change the directory path as we are moving from a mapped drive to the cloud). I was wondering if the below can be adjusted to do that?

Thanks

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

Haven't tried anything as no idea

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
Mark Petar
  • 1
  • 5
    Not an answer to your question: but you should change from hard coded path to e.g. a name (maybe hidden so that users aren't able to change it). Then it will be much easier to change it next time. – Ike Jan 16 '23 at 15:15
  • 2
    Have a look at https://stackoverflow.com/a/26515446/7446760 - it should give you enough to get going. – CLR Jan 16 '23 at 15:52
  • Welcome Mark! Please take some time to read the introduction to Stack Overflow and earn your first badge. https://stackoverflow.com/tour – user10186832 Jan 16 '23 at 18:21

0 Answers0