excel - How to identify ThisWorkbook module using VBA

Question

Using Excel 2010.

I need to add code to a remote Excel file where ThisWorkbook module has been renamed, let's say to "DashboardWorkbook". I don't know however the new name, can be anything but I need to identify this module programmatically in order to add more code to Sub Workbook_Open().

I am opening this remote file, then I go through all it's components:

Private Sub AddProcedureToWorkbook(wb As Workbook)
  Dim VBProj As VBIDE.VBProject
  Dim oComp As VBIDE.VBComponent
  Dim oCodeMod As VBIDE.CodeModule

  Set VBProj = wb.VBProject 
  For Each oComp In VBProj.VBComponents
    If *[check here if oComp was formerly ThisWorkbook but now renamed]* Then
      Set oCodeMod = oComp.CodeModule
        'add new code here
      ...etc, etc
    End If
  Next

End Sub

In Excel interface, ThisWorkbook has a different icon so it seems to be a different module type but I could not figure out what specific property to read in order to identify it?

To complicate things even more, sometimes Sub Workbook_Open() doesn't exist, therefore I need to add it at the right place...

Thank you,

M.R.

Answer 1

Sheets and books can be accessed directly from code by their CodeName (different from display name aka just Name).
This is also their VBComponent name.

Private Sub AddProcedureToWorkbook(wb As Workbook)
  Dim VBProj As VBIDE.VBProject
  Dim oComp As VBIDE.VBComponent
  Dim oCodeMod As VBIDE.CodeModule

  Set VBProj = wb.VBProject
  Set oComp = VBProj.VBComponents(wb.CodeName)
  Set oCodeMod = oComp.CodeModule

  oCodeMod.AddFromString "sub Hi()" & vbNewLine & "msgbox ""Hi.""" & vbNewLine & "end sub"
End Sub