Updating VBE from another workbook

[daz]

Hi Mr Excel

Do you know how to update the code of one workbook from another workbook?

I have over 400 users of a workbook, say called MainWorkbook, to process their data.
The users receive another workbook monthly, say called DataWorkbook, which is uploaded into the MainWorkbook.

I have been advised that the MainWorkbook has a slight bug. Instead of re-issuing a newly modified MainWorkbook, is there a way I can update the code in the MainWorkbook from the DataWorkbook?

As you can appreciate this is a bit different as I want the DataWorkbook to update the code in VBE.

Note: To make things difficult the MainWorkbook VBE is password protected.


DAZ from Brisbane

 

[Andrew Poulsom]

This might get you started (courtesy JWalk):

Sub UpdateUserBook()
    Filename = "UserBook.xls"
'   Activate workboook
    On Error Resume Next
    Workbooks(Filename).Activate
    If Err <> 0 Then
        MsgBox Filename & " must be open!", vbCritical
        Exit Sub
    End If
    Msg = "This macro will replace Module1 in UserBook.XLS "
    Msg = Msg & "with an updated Module." & vbCrLf & vbCrLf
    Msg = Msg & "Click OK to continue."
    If MsgBox(Msg, vbInformation + vbOKCancel) = vbOK Then
        Call ReplaceModule
    Else
        MsgBox "Module not replaced!", vbCritical
    End If
End Sub

Sub ReplaceModule()
'   Export Module1 from this workbook
    Filename = ThisWorkbook.Path & "tempmodxxx.bas"
    ThisWorkbook.VBProject.VBComponents("Module1") _
      .Export Filename
'   Replace Module1 in UserBook
    Set VBP = ActiveWorkbook.VBProject
    On Error GoTo ErrHandle
    With VBP.VBComponents
        .Remove VBP.VBComponents("Module1")
        .Import Filename
    End With
'   Delete the temorary module file
    Kill Filename
    MsgBox "The module has been replaced.", vbInformation
    Exit Sub

ErrHandle:
'   Did an error occur?
        MsgBox "ERROR. The module may not have been replaced.", _
          vbCritical
End Sub

Put your revised macro (with the same name as the original) and the above code in a new workbook. Change the file and module names as required.