Import module via vba instructions

[Thomas33]

Hi everyone,

I'm working on MS Excel 2010 and trying to have a macro duplicating a sheet(and it's associated macro "Update") in another workbook.

I manage to do almost everything except to import the required module "Update" from a precise and static folder ("Ressource" located in the same folder as all the workbooks) into my active workbook, which is surely possible. If you know about the required instructions, it would very kind of you to help me.

Moreover if you know a way to check if a module already exists and in that case overwrite it, I'd be even more grateful. Presently I just skip the error if it exists.

Here is my actual code:

 On Error GoTo ErrImport
ErrImport:
If Err.Number = 1004 Then
    MsgBox ("Already there")
    Resume Next    
End If

    Dim Path As String, MacroName as String
    
    Path = ThisWorkbook.Path & "\Ressource\"

    Application.VBE.ActiveVBProject.VBComponents.Import (Path & "Update.bas")
        
    On Error GoTo 0
   
    'Bonus: easy way to add a button and assign Macro to it
    
    AddM.CreateButton
    ActiveSheet.Shapes.Range(Array("Update")).Select
    MacroName = ActiveWorkbook.name & "!Update.Update"
    
    Selection.OnAction = MacroName

Thanks a lot!

 

[VoG]

You might want to look here Programming In The VBA Editor

 

[Thomas33]

Thanks a lot!