Want to Copy a Module(s) to another workbook in a different path and also password protect it at the same time using VBA

[all4excel]

Want to Copy a Module(s) to another workbook in a different path and also password protect it at the same time using VBA.

Dear Board,

I need to copy some Module(s) from One WorkBook to another using VBA..
I have used the following code to good effect, however my TargetWb is not at the same path, so I need some modification in this code if that's possible and also this VBA Module which is Copied and Pasted in the Target WOrkbook needs to be Password Protected so that it cannot be tampered.

The code which I'm using currently is:

Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
    TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
    Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
    strFolder = SourceWB.Path
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"
    On Error Resume Next
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
    On Error GoTo 0
End Sub

Sub Try()
Call CopyModule(Activeworkbook, "Module1", TargetWb)

Need help on this to pass the TargetWb as a variable which can be generated in the code used for generating the File using a Macro..
End Sub

In the above code The SourceWB would be the ActiveWorkbook and the Target Workbook is the file created using another Macro from several sheets of the Active Workbook and its created in a folder with a specific name and therefore the Path is one more level inside than the Active Workbook.

I also would appreciate if the Transferred Module can be Passworrd Protected and Locked for editing..for the end user.

Warm Regards
all4excel

 

[all4excel]

This is the link from which I got this amazing code however I just need some help to modify the code in order to pass a different address for the Target Workbook as well as if possible to make the Copy-Pasted code to be sent as Password Protected....

Copy modules from one workbook to another using VBA in Microsoft Excel

Regards
all4excel

 

[all4excel]

Bump No Response

Bump No Response..

I would appreciate if someone could help me with the small modification to add the address or the path of the Target Workbook and also if possible to make this transferred Module to be password protected in the Target Workbook...

Thanks in advance..
all4excel

 

[all4excel]

Can someone please help me with this request...

 

[all4excel]

Bump No Response

Bump No Response

 

[all4excel]

ANyone has any suggestions to improvise please it can really help..

 

[GooberTron]

I'm not sure about password protecting but your calling code should probably look something like

Sub Try()

    Dim SourceWB As Workbook
    Dim TargetWB As Workbook
    Dim strModuleName As String
    
    Set SourceWB = ActiveWorkbook
    Set TargetWB = Workbooks.Open("C:\Test\Yourworkbook.xlsm") 'Modify path here or use a variable
    strModuleName = "Module1"
    
    Call CopyModule(SourceWB, strModuleName, TargetWB)
     'TargetWB.Close SaveChanges:=True 'might not need this if your TargetWB was already open and hence you modify the above line Workbooks.Open

End Sub

I've used the same names as your main code though that isn't necessary.
I believe you will also need to set a reference to the VBA extensibility library for your other code to work.
Basically you need to set your TargetWB as a Workbook object at some point (you could pass it into the code above if you are calling this from another sub etc.)

 

[all4excel]

Dear Gobertron,

Thanks for the reply, i tried the code you provided however it did not copy paste the module in the targetwb..it just opens the tragetwb..

Please suggest I am not so good in VBA i have just used one of the codes from the net for this particular requirement...


Thanks & regards
all4excel

 

[GooberTron]

Make sure you have the Security Trust Centre to allow access to the VBA object model.
In 2010 this is;
File > Options > Trust Center > Trust Center Settings > Macro Settings
Then tick the box that says "Trust access to the VBA project object model

The code is working fine for me here

 

[all4excel]

I have Excel 2007..