Is it possible to edit excel vba code without opening workbook?

[jmacleary]

Hi folks.

I have inherited a complex set of 200+ workbooks which all have references to a master workbook embedded in the VBA. This reference is coded as a constant as follows:

Public Const ValidName As String = "masterbookname.xls"

Is there any way I can edit the VBA code without opening each workbook? Ideally I want to do a find and replace type operation to replace "masterbookname.xls" with "newmasterbook.xlsm".

Any help or ideas much appreciated.

 

[RoryA]

In a word, no. You'll have to open them, either manually or in code.

 

[Yongle]

Here is an excellent page but it may put you off trying to automate
http://www.cpearson.com/excel/vbe.aspx

Here is code that allows you to search the active vbProject. Results are written to immediate window.
credit : https://www.devhut.net/2016/02/24/vba-find-term-in-vba-modulescode/

Sub TestSearch()
    Dim sText As String
    sText = "[COLOR=#ff0000][I]Search for thi[/I]s[/COLOR]"
    Call findWordInModules(sText)
End Sub

Public Sub findWordInModules(ByVal sSearchTerm As String)
    Dim oComponent            As Object
    For Each oComponent In Application.VBE.ActiveVBProject.VBComponents
        If oComponent.CodeModule.Find(sSearchTerm, 1, 1, -1, -1, False, False, False) = True Then
            Debug.Print "Module: " & oComponent.Name  'Name of the current module in which the term was found (at least once)
            'Need to execute a recursive listing of where it is found in the module since it could be found more than once
            Call listLinesinModuleWhereFound(oComponent, sSearchTerm)
        End If
    Next oComponent
End Sub
 
Sub listLinesinModuleWhereFound(ByVal oComponent As Object, ByVal sSearchTerm As String)
    Dim lTotalNoLines         As Long   'total number of lines within the module being examined
    Dim lLineNo               As Long   'will return the line no where the term is found
 
    lLineNo = 1
    With oComponent
        lTotalNoLines = .CodeModule.CountOfLines
        Do While .CodeModule.Find(sSearchTerm, lLineNo, 1, -1, -1, False, False, False) = True
            Debug.Print vbTab & "Line No:" & lLineNo & Trim(.CodeModule.Lines(lLineNo, 1))  'Remove any padding spaces
            lLineNo = lLineNo + 1    'Restart the search at the next line looking for the next occurence
        Loop
    End With
End Sub

 

[jmacleary]

Here is an excellent page but it may put you off trying to automate

Thanks Yongle. I will give it a go - I will use the copy a module code.