PDA

View Full Version : Solved: Copy Module



RickSprague
08-17-2010, 05:05 AM
I am using Microsoft Visual Basic for Applications Extensibility library to copy modules from one workbook to another. I have the following code copied from another website:

Global VBAEditor As VBIDE.VBE
Global VBProj As VBIDE.VBProject
Global VBComp As VBIDE.VBComponent
Global CodeMod As VBIDE.CodeModule
Global xlApp As Object
Global wb As Object
Global ws As Object
Global FSO As Scripting.FileSystemObject
Global SourceFolder As Scripting.Folder

Function CopyModule(ModuleName As String, _
FromVBProject As VBIDE.VBProject, _
ToVBProject As VBIDE.VBProject, _
OverwriteExisting As Boolean) As Boolean

...code

End Function

My question is, how do I call this function in the Immediate Window? I tried:

? CopyModule("mdlTest","Book1","Book2",True) but, of course that doesn't work because Book1 & Book2 should be VBProjects. I'm stuck!!

Jan Karel Pieterse
08-17-2010, 07:00 AM
Not an answer to your question, but have you seen this tool:
http://www.jkp-ads.com/download.asp#copyvbaproject

RickSprague
08-17-2010, 07:10 AM
Thanks Jan. That is so close, except I need to do a lot of workbooks.

Jan Karel Pieterse
08-18-2010, 12:03 AM
You can study its code to find out how to address the VBProject of a specific workbook.

RickSprague
08-18-2010, 12:20 AM
... except it is password protected

Jan Karel Pieterse
08-18-2010, 12:27 AM
:-) Sorry 'bout that.
The relevant pieces are:

This is the VBProject of a workbook:
Workbooks("Book1").VBProject
So your function call is:
CopyModule "mdlTest", Workbooks("Book1").VBProject, Workbooks("Book2").VBProject, True

RickSprague
08-18-2010, 11:40 AM
Brilliant

dank u zeer :)