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

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,026
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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:
Code:
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.
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
In a word, no. You'll have to open them, either manually or in code.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,788
Office Version
  1. 365
Platform
  1. Windows
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/
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,436
Members
409,877
Latest member
DDhol
Top