CODEWITHSUNDEEP
vbaexcel
JChristen
JChristen

Reputation: 608

VBA - Getting the modules in workbook

I'm trying to create a workbook that is used for creating other .xlsm workbooks, but can't figure out how to get the modules I need so I can add them.

My code as it stands is below (modified from the answer given here: How to add excel 2010 macro programmatically)

The place I need help is in the ImportModules sub, by the comment 'LIST MODULES HERE

How can I get an array of modules that are in the current workbook?

Private Sub SVAmaker_Click()

    Dim file As String
    file = InputBox("SVA Planner file name", "Name", "Name")

    Application.DefaultSaveFormat = xlOpenXMLWorkbookMacroEnabled
    Workbooks.Add
    ActiveWorkbook.SaveAs filename:=file

    Dim WB As Workbook
    WB = ActiveWorkbook
    Call ImportModules(VBA.CStr(WB))

End Sub

Sub ImportModules(sWorkbookname As String)

    Dim cmpComponents As VBIDE.VBComponents
    Dim wbkTarget As Excel.Workbook

    Set wbkTarget = Workbooks.Open(sWorkbookname)

    If wbkTarget.VBProject.Protection = 1 Then
        Debug.Print wbkTarget.Name & " has a protected project, cannot import module"
    GoTo Cancelline
    End If

    Set cmpComponents = wbkTarget.VBProject.VBComponents

    Dim vModules As Variant
    'LIST MODULES HERE

    Dim i As Integer
    For i = LBound(vModules) To UBound(vModules)
        cmpComponents.Import vModules(i)
    Next i

Cancelline:

    If wbkTarget.FileFormat = xlOpenXMLWorkbook Then
        wbkTarget.SaveAs wbkTarget.Name, xlOpenXMLWorkbookMacroEnabled
        wbkTarget.Close SaveChanges:=False
    Else
        wbkTarget.Close SaveChanges:=True
    End If

    Set wbkTarget = Nothing

End Sub

Upvotes: 3

Views: 10680

Answers (5)

Joe Phi
Joe Phi

Reputation: 350

JChristen asked for a list of those modules

I'd create a collection, based on gizlmo's proposal:

    Dim vbcomp As VBComponent
    Dim modules as Collection

    set modules = new Collection
    For Each vbcomp In ThisWorkbook.VBProject.VBComponents

        'if normal or class module
        If ((vbcomp.Type = vbext_ct_StdModule) _
             Or _
            (VBComp.Type = vbext_ct_ClassModule)) Then 

           modules.add VBcomp.name

        End If
    Next vbcomp

Later on you can use this collection like this:

    Dim module     as Variant
    for each module in modules
        ' e.g. importing the module 
        import module
    next module

hope it helps

Upvotes: 7

user3598756
user3598756

Reputation: 29421

Why not simply make a copy of the "master" workbook you'd import modules from

Option Explicit

Private Sub SVAmaker_Click()

    Dim fso As New FileSystemObject
    Dim myFile As file        
    Dim fileName As String

    fileName = InputBox("SVA Planner file name", "Name", "Name") & ".xlsm"

    Set myFile = fso.GetFile(ActiveWorkbook.FullName)
    fso.CopyFile myFile, myFile.ParentFolder & "\" & fileName

End Sub

from here on you have a new workbook with all modules (and sheets) already there.

should you need to delete some worksheets you juts open it and act with "plain" VBA Excel Model Object code

in order to use FileSytemObject API, you need to reference "Microsoft Scripting Runtime"

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

This code should help. It will export all modules to the desktop, create a new workbook and import them all into it.

Public Sub ExportImportAllModules()

    Dim srcVBA As Variant
    Dim tgtVBA As Variant
    Dim srcModule As Variant
    Dim wrkBk As Workbook
    Dim sDeskTop As String

    On Error GoTo ERROR_HANDLER

    Application.DisplayAlerts = False

    Set srcVBA = ThisWorkbook.VBProject
    sDeskTop = CreateObject("WScript.Shell").specialfolders("Desktop")
    Set wrkBk = Workbooks.Add(xlWBATWorksheet) 'New workbook with 1 sheet.
    Set tgtVBA = wrkBk.VBProject

    For Each srcModule In srcVBA.vbComponents
        'There may be a better way to check it's a module -
        'I'm making it up as I go along.
        If srcModule.Type = 1 Then 'vbext_ct_StdModule
            srcModule.Export sDeskTop & "\" & srcModule.Name
            tgtVBA.vbComponents.Import sDeskTop & "\" & srcModule.Name
            Kill sDeskTop & "\" & srcModule.Name
        End If
    Next srcModule

    Application.DisplayAlerts = True

    On Error GoTo 0
    Exit Sub

ERROR_HANDLER:
    Select Case Err.Number

        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure ExportImportAllModules."
            Err.Clear
            Application.EnableEvents = True
    End Select

End Sub

Upvotes: 0

gizlmo
gizlmo

Reputation: 1922

You can loop through all modules with an easy For Each Loop. Requires a Reference to "Microsoft Visual Basic for Applications Extensibility"!

Dim vbcomp As VBComponent

For Each vbcomp In ThisWorkbook.VBProject.VBComponents

    'if normal Module
    If vbcomp.Type = vbext_ct_StdModule Then

        'Do Stuff
    End If
Next vbcomp

with .Type you can check the type of the Module(Form, Normal Module, ClassModule etc)

Upvotes: 1

holmicz
holmicz

Reputation: 577

you can go through the modules like this. Create some collection and then iterate over all objects in VBComponents of VBProject (Value of type for module is 1):

'declare some collection, which will contain modules
For Each vbc In ThisWorkbook.VBProject.VBComponents
   if vbc.Type = 1 then
       'add to temporary collection ... for example for name, use vbc.name
   end if
Next

Upvotes: 1

Related Questions