Friday, July 9, 2010

Move the Excel VBA Code from One Workbook to Another.

Hi All,

Recently, I wanted to move VBA Code from one workbook to another. I didn't have any idea. So my teammate did me a favor and got me the following code. So I thought that it is good to share it with the outside world. Moreover, it also moves the code with the Forms as well.

The function expects two parameters,

sourceBook: the workbook where we have to copy the code from
destBook : the workbook where the code has to be copied to

Sub MoveVBACode(sourceBook As Workbook, destBook As Workbook)

Dim sourceVbComp As VBIDE.VBComponents
Dim destVbComp As VBIDE.VBComponents

Dim vbComp As VBComponent

'To check the code is protected or not
If sourceBook.VBProject.Protection = vbext_pp_locked Then
MsgBox "Can't copy code because Source project code is password protected", vbInformation
Exit Sub
End If

Set sourceVbComp = sourceBook.VBProject.VBComponents
Set destVbComp = destBook.VBProject.VBComponents

'To copy the modules from source to destination
For Each vbComp In sourceVbComp
If vbComp.Type = vbext_ct_Document Then
CopyModule vbComp, destVbComp, ""
ElseIf vbComp.Type = vbext_ct_StdModule Then
CopyModule vbComp, destVbComp, ".bas"
ElseIf vbComp.Type = vbext_ct_MSForm Then
CopyModule vbComp, destVbComp, ".frm"
ElseIf vbComp.Type = vbext_ct_ClassModule Then
CopyModule vbComp, destVbComp, ".cls"
End If

End Sub

Function CopyModule(sourceVbComp As VBIDE.VBComponent, _
destVbComp As VBIDE.VBComponents, _
extn As String)

Dim fName As String
Dim vbComp As VBIDE.VBComponent
Dim destCodeMod As VBIDE.CodeModule
Dim codeText As String
Dim destCodeModule As Variant
Dim TempVBComp As Variant

Const filePath As String = "C:\"
Const xportPath As String = "C:\Temp\"

fName = xportPath & sourceVbComp.Name & extn

sourceVbComp.Export fName

If sourceVbComp.Type = vbext_ct_Document Then
'To copy the code form ThisWorkbook and sheets

'To get the code module in the destination file
For Each vbComp In destVbComp
If vbComp.Type = vbext_ct_Document And vbComp.Name = sourceVbComp.Name Then
Set destCodeModule = vbComp.CodeModule
Exit For
End If

'To update the code from source to destination
If Not destCodeModule = "" Then
Set TempVBComp = destVbComp.Import(fName)
With destCodeModule
If TempVBComp.CodeModule.CountOfLines > 0 Then
codeText = TempVBComp.CodeModule.Lines(1, TempVBComp.CodeModule.CountOfLines)
.InsertLines 1, codeText
End If
End With
destVbComp.Remove TempVBComp
End If

Else ' For modules ,forms and classes
destVbComp.Import fName
End If

'To kill the exported file
Kill fName

End Function



JP said...

You failed to mention that the code requires an early bound reference to Microsoft Visual Basic for Applications Extensibility which on my system is located at C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL.

Also, "vbext_pp_locked" looks like a constant but isn't defined anywhere.

Mr VBA said...

Nice info! Visit my blog vba tutorial Just started to blog about my favorite topic! Can we exchange link? Leave comment if agree..

Vikas Bhandari said...

Hi JP,

It is nice that you pointed out about the reference for MS VBA Extensibility pack which actually owns the libraries for Code Module. Thanks for that. Without adding the reference, this code cannot be used. Thanks to you.

However, you have missed the info about "vbext_pp_locked" constant. This is defined under the "VBIDE.vbext_ProjectProtection"


Roy Cox said...

Of course this won't work if the VBA Project is password protected.

You only need to manually drag & drop the modules into the target workbook

Vikas Bhandari said...

Yeah, that is right! This code shows a message if the workbook is password protected. Anyways, one more thing... if you want this code to work, it will require the reference to C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB