Importing Excel Macros Code from another Excel sheet which has Macro code.

How can I Export and import Excel Macro from an Excel Template in Centralized location? Whenever user opens the Template this code gets imported at runtime.

All macro code in file1 kept in Shared drive.

Whenever any user opens Template it gets all the code for macro from this Shared Drive --> template.



For that I need to have some code within all the templates to get this code from shared drive.

i) Importing macros

ii) have all code in VBS format in the shared drive



But for First solution it not working. I will show you what I have worked on and could you please tell me what is wrong.

Please find the code which i have worked on however has issues with it when import line is execute. It doesn't perform any action though it executes that line.

Private Sub Workbook_Open()
'Call Sheet1.updatescreens
Call OpenSourceExcel
Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Test Script")
'Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Result")
'Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Datapool")
'Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Object Repository")
Call CloseSourceExcel
End Sub

Sub CopyMacroModule(SourceWB As Workbook, strModuleName As String)
strFolder = SourceWB.Path
arun = ThisWorkbook.Path
arun1 = ThisWorkbook.Name
Set Targetwb = ThisWorkbook
MsgBox arun1
temp = arun & "\" & arun1
MsgBox temp
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & "\"
strTempFile = strFolder & "tmpexport.bas"
MsgBox strFolder
SourceWB.VBProject.VBComponents(1).Export (strTempFile)
MsgBox ActiveWorkbook.Name
Targetwb.VBProject.VBComponents.Import (strTempFile)
ThisWorkbook.AcceptAllChanges
ThisWorkbook.Save
Kill strTempFile
End Sub


Private Sub OpenSourceExcel()
strSourceSheet = "C:\Smoke_Test.xls"
Set objExcelSource = CreateObject("Excel.Application")
Set objSpreadSource = objExcelSource.Workbooks.Open(strSourceSheet)
End Sub
Private Sub CloseSourceExcel()
objExcelSource.Workbooks.Close
End Sub