ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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      Printerfriendly version

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address