1

I'm stepping through the code to export a module using this post, but nothing happens. Is there a security setting to allow VBA permission to export a module?

I'm copying a few tabs from a workbook into a new workbook, but the tabs have macros which lead to broken links. To get around this I want to move the module and re-associate the macro. If I can't get this to work I will just copy the whole workbook and delete the info I don't want in the destination.

Here's the code from the above post: 

Public Sub CopyModule(SourceWB As Workbook, strModuleName As String, TargetWB As Workbook)

' Description:  copies a module from one workbook to another
' example: CopyModule Workbooks(ThisWorkbook), "Module2",
'          Workbooks("Food Specials Rolling Depot Memo 46 - 01.xlsm")
' Notes:   If Module to be copied already exists, it is removed first,
'          and afterwards copied

Dim strFolder                       As String
Dim strTempFile                     As String
Dim FName                           As String

If Trim(strModuleName) = vbNullString Then
    Exit Sub
End If

If TargetWB Is Nothing Then
    MsgBox "Error: Target Workbook " & TargetWB.Name & " doesn't exist (or closed)", vbCritical
    Exit Sub
End If

strFolder = SourceWB.Path
If Len(strFolder) = 0 Then strFolder = CurDir

' create temp file and copy "Module2" into it
strFolder = strFolder & "\"
strTempFile = strFolder & "~tmpexport.bas"

On Error Resume Next
FName = Environ("Temp") & "\" & strModuleName & ".bas"
If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
    Err.Clear
    Kill FName
    If Err.Number <> 0 Then
        MsgBox "Error copying module " & strModuleName & "  from Workbook " & SourceWB.Name & " to Workbook " & TargetWB.Name, vbInformation
        Exit Sub
    End If
End If

' remove "Module2" if already exits in destination workbook
With TargetWB.VBProject.VBComponents
    .Remove .Item(strModuleName)
End With

' copy "Module2" from temp file to destination workbook
SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile

Kill strTempFile
On Error GoTo 0

End Sub

user6768333
  • 11
  • 3
  • Even though you are referencing another post, you still should post the exact code you are trying to execute. You may also want to try post a comment that the post you mentioned instead of starting a new question. – JamesRLamar Sep 11 '17 at 21:20
  • Yeah, I agree. I tried to comment on the other one but I don't have enough status points yet. I'll try to paste the code in. – user6768333 Sep 12 '17 at 01:41
  • Ah yes, forgive me. Good job posting the code. Others may ask for your environment details as well, such as OS and VB version. Doesn't hurt to post that kind of info in case it is relevant to your solution. – JamesRLamar Sep 12 '17 at 13:13

0 Answers0