Results 1 to 3 of 3

Thread: Replace Directory name referenced in ALL Macros in a directory?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Replace Directory name referenced in ALL Macros in a directory?

    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?

  2. #2
    New Member
    Join Date
    Dec 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace Directory name referenced in ALL Macros in a directory?


  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace Directory name referenced in ALL Macros in a directory?

    Quote Originally Posted by imranbhatti View Post
    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/...ms-office-2013
    Code:
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •