CODEWITHSUNDEEP
excelvba
user5326167
user5326167

Reputation:

How to identify ThisWorkbook module using VBA

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.

Upvotes: 4

Views: 935

Answers (2)

GSerg
GSerg

Reputation: 78134

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

Upvotes: 5

chris neilsen
chris neilsen

Reputation: 53135

Each of the VBProj.VBComponents items has a Properties collection. The set of properties of the Workbook object is different to the others (Sheets, Modules etc).

Pick a unique property of the Workbook and search the components collection for that.

Try this

Private Function FindThisWorkbook(wb As Workbook) As VBIDE.VBComponent
    Dim VBProj As VBIDE.VBProject
    Dim oComp As VBIDE.VBComponent
    Dim oP As Property

    Set VBProj = wb.VBProject
    For Each oComp In VBProj.VBComponents
        Set oP = Nothing
        On Error Resume Next
        Set oP = oComp.Properties("ActiveSheet")
        On Error GoTo 0
        If Not oP Is Nothing Then
            ' Found it
            Set FindThisWorkbook = oComp
            Exit Function
        End If
    Next

End Function

Upvotes: 2

Related Questions