How to insert code into another Excel workbook procedure module? 1

[LSTAN]

Hi,

Need to open a second workbook and insert codes into it's Private Sub Workbook_BeforeClose(Cancel As Boolean) event. Can it be done? If possible, how??

regards

 

[acron]

A similar situation arose here very recently, but as you require changes to the Workbook object code a slightly different approach is called for, as presumably you do not want to replace the complete workbook. You should code your new Before_Close procedure and include with it all the other procedures (if any) that are included in ThisWorkbook object, such as Before_Save and Open etc, as the procedure I recommend means repacing all the code. When you have the code in place copy it to a text file (do not export a module) and distribute teh text file with a workbook that contains the following code.

Sub UpDateModule()
Dim i As Long
    Workbooks("WBToUpdate.xls").Activate
    With ActiveWorkbook.VBProject.VBComponents _
        (ThisWorkbook.CodeName).CodeModule
        If .CountOfLines > 0 Then
            For i = .CountOfLines To 1 Step -1
                .DeleteLines i
            Next
        End If
        .AddFromFile ("UpDate.txt")
    End With
End Sub

You need to take account of the path to the text file, and also ensure that the the workbook that needs updating (WBToUpdate.xls in the example above)is open before running this code.

AC