How to access the contents of a macro in a file from another macro/script?

Question

As part of a data migration process, I need a script/macro to access all files in the source location and check for interlinked documents so that those links can be re-established post migration (already completed). Apart from direct links, if a file has a macro that is trying to access another file, that link should also be restored post migration(need a solution for this).

So basically, is there a way the access the contents of a macro in a file from another script/macro (to check for the documents the first macro is trying to access so that it works even post migration)?

Answer 1

Yes. You can actually read the contents of a module (or any VBA project component) by programming the Visual Basic Editor (VBE) itself. You can do so by following below steps.

1. Add reference to VBA Extensibility Library

   Microsoft Visual Basic For Applications Extensibility 5.3

2. Once successful, you can then write a code to retrieve the lines of code from another module or project in another workbook. Something like below which prints codes found in a standard module:

   Dim VBProj As VBIDE.VBProject
   Dim VBComp As VBIDE.VBComponent
   Dim VBCodeMod As VBIDE.CodeModule
   
   Dim wb As Workbook, i As Integer
   
   Set wb = ThisWorkbook '/* or any target workbook */
   '/* You can actually create a loop which opens all workbooks in a directory */
   
   Set VBProj = wb.VBProject
   
   '/* For simplicity sake, this prints all codes in a Standard Module */
   For Each VBComp In VBProj.VBComponents
       If VBComp.Type = vbext_ct_StdModule Then
           Set VBCodeMod = VBComp.CodeModule
           With VBCodeMod
               For i = 1 To .CountOfLines
                   '/* This prints the lines in the module */
                   Debug.Print .Lines(i, 1)
                   '/* You can transfer this instead somewhere */
               Next
           End With
       End If
   Next
   

3. You can use the Find method if you need specific lines only:

   Dim VBProj As VBIDE.VBProject
   Dim VBComp As VBIDE.VBComponent
   Dim VBCodeMod As VBIDE.CodeModule
   
   Dim wb As Workbook, IsFound As Boolean
   Dim StartLine As Long, EndLine As Long, StartCol As Long, EndCol As Long
   
   Set wb = ThisWorkbook
   Set VBProj = wb.VBProject
   
   For Each VBComp In VBProj.VBComponents
       If VBComp.Type = vbext_ct_StdModule Then
           Set VBCodeMod = VBComp.CodeModule
           With VBCodeMod
   
               StartLine = 1
               EndLine = .CountOfLines
               StartCol = 1
               EndCol = 255
   
               '/* Below finds a specific pattern, e.g. directory */
               '/* patternsearch argument set to true */
               IsFound = .Find("C:\*", StartLine, StartCol, _
                   EndLine, EndCol, False, False, True)
   
               Do Until IsFound = False
                   Debug.Print .Lines(StartLine, 1) '/* Prints the found pattern */
                   EndLine = .CountOfLines
                   StartCol = EndCol + 1
                   EndCol = 255
                   IsFound = .Find("C:\*", StartLine, StartCol, _
                       EndLine, EndCol, False, False, True)
               Loop
           End With
       End If
   Next
   

Not sure if this answers your specific question but hope this gets you started.
Btw, it is important to tick Trust access to the VBA project object model under Developer Macro Settings to make this work.

You can find above under Developer Tab > Code > Macro Security.
And of course the project should be unlocked.