From VB program programmatically insert event procedure into Excel

From VB program programmatically insert event procedure into Excel
I have a group of Excel worksheets that contain many Visual Basic event procedures and modules. I would like to be able to update the code in these worksheet procedures and modules directly from an external Visual Basic program. In the external Visual Basic program I have a routine that defines the Excel worksheet I want to change and have had no problem in extracting or changing information in the Excel worksheet cells. In the external Visual Basic program the following is what I use to access information contained in my Excel worksheets.

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("C:\Excel\MyWorksheet.xls", , False)
Set xlSheet = xlBook.Sheets(3)

How can I Import, Remove, or Change modules or event procedures to the Excel worksheet from my external Visual Basic program? If anybody knows how to do this would you please provide me with a sample.

Hi

Defining Objects

VB Code:

'VBProject                
Dim VBProj As VBProject
Set VBProj = ThisWorkbook.VBProject 
 
'VBComponent                
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("Module1") 
 
'CodeModule                
Dim VBCodeMod As CodeModule
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

Importing A Module To A Workbook

There isn't a single method to copy modules from one VBProject to another. Instead, you have to export the module from one project, and then import it into another. The following procedure will copy Module1 from Book2 to Book1.

VB Code:

Sub CopyOneModule()
 
Dim FName As String
With Workbooks("Book2")
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("Module1").Export FName
End With
Workbooks("book1").VBProject.VBComponents.Import FName
 
End Sub

Just change "Module1" to the name of the module you want to copy. If you want to copy all modules (except the ThisWorkbook and Sheet modules), you can use the following code.

VB Code:

Sub CopyAllModules()
 
Dim FName As String
Dim VBComp As VBIDE.VBComponent
 
With Workbooks("Book2")
    FName = .Path & "\code.txt"
    If Dir(FName) <> "" Then
        Kill FName
    End If
    For Each VBComp In .VBProject.VBComponents
        If VBComp.Type <> vbext_ct_Document Then
           VBComp.Export FName
           Workbooks("book1").VBProject.VBComponents.Import FName
           Kill FName
        End If
    Next VBComp
End With
End Sub

Removing/Deleting A Procedure From A Module

The procedure below will delete the procedure called "MyNewProcedure" from the module named "NewModule" in ThisWorkbook.

VB Code:

Sub DeleteProcedure()
 
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
 
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
    StartLine = .ProcStartLine("MyNewProcedure", vbext_pk_Proc)
    HowManyLines = .ProcCountLines("MyNewProcedure", vbext_pk_Proc)
    .DeleteLines StartLine, HowManyLines
End With
 
End Sub

Removing/Deleting All Code From A Module

The procedure below will delete all code from a module name "NewModule".

VB Code:

Sub DeleteAllCodeInModule()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
 
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
    StartLine = 1
    HowManyLines = .CountOfLines
   .DeleteLines StartLine, HowManyLines
End With
End Sub

Hope this helps....

Thank you for your response. This looks like just what I was looking for.