Replace Directory name referenced in ALL Macros in a directory?

[jeffcoleky]

We just changed from Dropbox to OneDrive. I have many macros and files that reference c:\dropbox\directoryname and I need to change them ALL to read c:\onedrive\directoryname instead.

Can someone help me write a macro that would open each XLSM file in c:\files, then change the word "dropbox" to "onedrive" within all macros of each file?

 

[imranbhatti]

See if this helps : http://www.vbaexpress.com/forum/archive/index.php/t-44221.html

 

[jeffcoleky]

See if this helps : http://www.vbaexpress.com/forum/archive/index.php/t-44221.html

Thanks imranbhatti but I couldn't figure out your link code. But you encouraged me to try some different searches in Google I hadn't done yet to try to find my answer. As a result I found this code which works for "open" workbooks only.

Could you help me modify it so it works on all files in a specified directory?

Source: https://stackoverflow.com/questions...replace-code-in-vba-modules-in-ms-office-2013

Option Explicit

Sub ReplaceTextInCodeModules()


' Must add a reference to "Microsoft Visual Basic For Applications Extensibility 5.3"
' Also must set "Trust access to the VBA project object model"
' See the url below for more info on these.
' Based on code found at:
' Source: www.cpearson.com/excel/vbe.aspx Copyright 2013, Charles H. Pearson


Dim theWorkbook As Workbook
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim numLines As Long ' end line
Dim lineNum As Long
Dim thisLine As String
Dim message As String
Dim numFound As Long


Const FIND_WHAT As String = "onedrive"
Const REPLACE_WITH As String = "onedrive"


    numFound = 0
' c:\onedrive\test1


    For Each theWorkbook In Application.Workbooks
        If theWorkbook.Name <> ThisWorkbook.Name Then
            If theWorkbook.HasVBProject Then
                Set VBProj = theWorkbook.VBProject
                For Each VBComp In VBProj.VBComponents
                    'Set VBComp = VBProj.VBComponents("Module1")
                    Set CodeMod = VBComp.CodeModule


                    With CodeMod
' c:\onedrive\test1
                        numLines = .CountOfLines
                        For lineNum = 1 To numLines
                            thisLine = .Lines(lineNum, 1)
                            If InStr(1, thisLine, FIND_WHAT, vbTextCompare) > 0 Then
                                message = message & theWorkbook.Name & " | " & VBComp.Name & " | Line #" & lineNum & vbNewLine
                                .ReplaceLine lineNum, Replace(thisLine, FIND_WHAT, REPLACE_WITH, , , vbTextCompare)
                                numFound = numFound + 1
                            End If
                        Next lineNum
                    End With
                Next VBComp
            End If
        End If
    Next theWorkbook


    Debug.Print "Found: " & numFound
    If message <> "" Then
        Debug.Print message
    End If


End Sub