Export and Import a module through VBA coce

Question

Hi,

I have a macro that is validating the dropdown list. I want to include the same validation in 350 files in a folder. I am trying to export and import the module through VBA code. But, somehow it does not work.

Below is the code, I am using

Public Sub Main()

Dim SourceWB As Workbook
Dim WB2 As Workbook

Set SourceWB = Workbooks("Source File.xlsm")
Set WB2 = Workbooks("Destination File.xlsm")

Call CopyModule1(SourceWB, "Module1", WB2)

End Sub

Sub CopyModule1(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"  
'MsgBox (strTempFile)  
On Error Resume Next  
  
SourceWB.VBProject.VBComponents(strModuleName).Export (strTempFile)  
TargetWB.VBProject.VBComponents.Import (strTempFile)  
MsgBox (SourceWB.VBProject)  
Kill strTempFile  
On Error GoTo 0  

End Sub