Import modules and replace existing, loop.

staticbob

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
Guys,

I am using this code to transfer code from one workbook to another. I need to upgrade 3 forms and 2 modules. How do I loop through these. AND, ensure that the modules and forms in the target workbook are overwritten or deleted first.

The Application.Dialogs(xlDialogActivate).Show line is not making the workbook selected active ???

I need to upgrade - frmsavewr, frmsavecr, Mdl_Sendmail, Md_spellcheck, Mdl_Toolbar.

Thanks in advance !
Bob

EDIT - I have now figured out that I need to delete the modules from the target workbook first, I've added code to do this.

Code: 
Private Sub upgrade()
'Select target workbook

Dim TWB As Workbook 'Target Workbook
Dim UWB As Workbook 'Upgrade Workbook
Dim FName As String 'temp filename for export / import

Application.ScreenUpdating = False
Set UWB = ActiveWorkbook

MsgBox "Please select the Workbook to upgrade" & vbNewLine & "from the following list . . ." & vbNewLine & "(Click OK to show)"
Application.Dialogs(xlDialogActivate).Show
Set TWB = ActiveWorkbook

'delete existing modules in Target WB
Dim VBComp As VBComponent
On Error Resume Next
With TWB
    Set VBComp = ThisWorkbook.VBProject.VBComponents("frmsavewr")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("frmsavecr")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_Sendmail")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_spellcheck")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_Toolbar")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
End With

'import / export code
With UWB
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("frmsavewr").Export FName
End With
TWB.VBProject.VBComponents.Import FName

With UWB
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("frmsavecr").Export FName
End With
TWB.VBProject.VBComponents.Import FName

With UWB
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("Mdl_Sendmail").Export FName
End With
TWB.VBProject.VBComponents.Import FName

With UWB
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("Mdl_spellcheck").Export FName
End With
TWB.VBProject.VBComponents.Import FName

With UWB
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("Mdl_Toolbar").Export FName
End With
TWB.VBProject.VBComponents.Import FName

Application.ScreenUpdating = True
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Hi Bob,

Only briefly tested, but how about:
Code: 
Sub Main()
    Dim wbkSource As Workbook
    Dim wbkToChange As Workbook
    Dim varProcs As Variant, lCnt As Long
    
    Set wbkSource = Workbooks("ABook")
    Set wbkToChange = Workbooks("BBook")
    
    varProcs = Array("frmsavewr", "frmsavecr", "Mdl_Sendmail", "Md_spellcheck", "Mdl_Toolbar")
    
    For lCnt = LBound(varProcs) To UBound(varProcs)
        DeleteComp wbkToChange, varProcs(lCnt)
        ExportImport wbkSource, wbkToChange, varProcs(lCnt)
    Next lCnt
    
End Sub

Sub DeleteComp(wbk As Workbook, ByVal strProc As String)
    Dim VBComp As VBComponent
    
    On Error Resume Next
    Set VBComp = wbk.VBProject.VBComponents(strProc)
    On Error GoTo 0
    If Not VBComp Is Nothing Then
        wbk.VBProject.VBComponents.Remove VBComp
    End If
    
End Sub

Sub ExportImport(wbkSource As Workbook, wbkToChange As Workbook, ByVal strProc As String)
    Dim FName As String
    Dim VBComp As VBComponent
    
    With wbkSource
        FName = .Path & "\code.txt"
        On Error Resume Next
        Set VBComp = .VBProject.VBComponents(strProc)
        On Error GoTo 0
        If Not VBComp Is Nothing Then
            VBComp.Export FileName:=FName
            wbkToChange.VBProject.VBComponents.Import FileName:=FName
        End If
    End With

End Sub
HTH
 
Upvote 0
Thanks Richie,

That looks good mate, but I need the users to select the workbook to upgrade from the currently open files, ie - the window menu ??

Any ideas ?
Bob
 
Upvote 0
Guys,

Why is this code not setting TWB to the selected book ?

Application.Dialogs(xlDialogActivate).Show
Set TWB = ActiveWorkbook

Thanks
bob
 
Upvote 0
Hi Bob,

If you want the users to pick a workbook from those currently open then try this simple amendment:
Code: 
Sub Main()
    Dim wbkSource As Workbook
    Dim wbkToChange As Workbook
    Dim varProcs As Variant, lCnt As Long
    
    Set wbkSource = Workbooks("ABook")
    Set wbkToChange = ActiveWorkbook
        
    If MsgBox(prompt:="Update this workbook? : " & wbkToChange.Name, _
        Buttons:=vbYesNo) = vbYes Then
        varProcs = Array("frmsavewr", "frmsavecr", "Mdl_Sendmail", "Md_spellcheck", "Mdl_Toolbar")
        
        For lCnt = LBound(varProcs) To UBound(varProcs)
            DeleteComp wbkToChange, varProcs(lCnt)
            ExportImport wbkSource, wbkToChange, varProcs(lCnt)
        Next lCnt
    End If
    
End Sub
If you want them to pick an unopen file then have a look at GetOpenFilename.

HTH
 
Upvote 0
Richie,

I cannot see how users can pick which workbook to update from your ammended code ? The msgbox line just asks them if they want to update the active workbook ?

What I really need, is a Prompt, with a drop-down or list of currently opened workbook. "Which workbook do you want to upgrade" Thats what I was trying to do in the OP with Application.Dialogs(xlDialogActivate).Show . . . . but even when the users select a workbook, it doesn't activate it.

??
Thanks
Bob
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question
Export macro with location referenced in Cell
Replies
0
Views
461
YolandaCB
Y
S
  • Solved
VBA to Copy a Module to Another Workbook
Replies
3
Views
14K
rollis13
rollis13
C
  • Solved
Replace Module w/ VBA
2
Replies
10
Views
4K
rollis13
rollis13
J
  • Question
vba script to copy modules from one book to another
Replies
2
Views
498
JannetteChristie
J
C
  • Question
Ton of script in case anyone wants to review, maybe something useful to someone
Replies
0
Views
2K
cizzett
C

Forum statistics

Threads
1,215,757
Messages
6,126,693
Members
449,331
Latest member
smckenzie2016

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top