Insert a new module from a file

With the macro below you can easily insert new modules with contents in a workbook. This requires that you have created the new module previously in another (temporary) workbook. Export the finished module to a text file by rightclicking the module name and select Export file... in the shortcut menu.

Sub InsertVBComponent(ByVal wb As Workbook, ByVal CompFileName As String)
' requires a reference to the Microsoft Visual Basic Extensibility library
' inserts the contents of CompFileName as a new component in wb
' CompFileName must be a valid VBA component suited for 
' import (an exported VBA component)
    If Dir(CompFileName) <> "" Then 
        ' source file exist
        On Error Resume Next ' ignores any errors if the project is protected
        wb.VBProject.VBComponents.Import CompFileName 
        ' inserts component from file
        On Error GoTo 0
    End If
    Set wb = Nothing
End Sub

Example:

InsertVBComponent ActiveWorkbook, "C:\FolderName\Filename.bas"

Document last updated 2000-02-05 12:51:07 Printer friendly version