Macro to export and import a module and assigning different macro automatically

**Libster78** · 01-27-2010, 06:06 AM

I have had a go at this but I have clearly not got it right.

The code I have used is:

Code:

Sub Copy_Save()
 


Dim myFileName
    
Sheets(Array("Purchase Order", "Continuation Sheet")).Copy

myFileName = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")

If myFileName = False Then

    MsgBox "Save cancelled", vbCritical
    Exit Sub
    
End If

    ActiveWorkbook.SaveAs myFileName
    Macromove
    ActiveWorkbook.Protect
    ActiveWorkbook.Close
    


End Sub
Sub Macromove()

ActiveWorkbook.VBProject.VBComponents("module2").Export ("c:\MrXL1.bas")

Application.VBE.ActiveVBProject.VBComponents.Import("c:\MrXL1.bas").VBComponents("module2").Export ("c:\MrXL1.bas")


End Sub

I would like the sheets Purchase order and Continuation sheet to be copied and saved which the Copy_Save Macr does however when this does this I want module 2 in my VBE to go with the new sheet.

I'm sure it can work but i have not done it right .

Any help gratefully recieved.

Thanks

**pike** · 01-27-2010, 07:22 AM

Hi Libster78
I use this
where CopyAMouldle( wkb From, wkbTo , "Module2")

Code:

Option Explicit

Sub CopyAModule(wkbFrom As String,  wkbTo As String, strFromMod As String)
    Dim wkb As Workbook
    Dim strFile As String
    
    Set wkb = Workbooks(wkbFrom)
    
    strFile = wkb.Path & "\vbCode.bas"
    wkb.VBProject.VBComponents(strFromMod).Export strFile
    
    On Error Resume Next
    Set wkb = Workbooks(wkbTo)
    If Err.Number <> 0 Then
        Workbooks.Open wkbTo
        Set wkb = Workbooks(wkbTo)
    End If
    
    wkb.VBProject.VBComponents.Import strFile
    wkb.Save
    
    Set wkb = Nothing
End Sub

**Libster78** · 01-27-2010, 07:38 AM

I can't get this to work. I want it to only export Module 2 of the 2 modules and ignore module 1.

So I click Print button on original workbook, it prints the sheets requestes, transfers specific data onto a 3rd sheet, copies the printed sheets in to a new workbook, saves it.

I want to be able to go into this newly formed workbook click print button as before the difference being that it prints, transfers the data onto my original workbook summary sheet, then saves.

The module one for original workbook is:

Code:

Private Sub Workbook_Open()
    Sheets("Purchase Order", "Contiuation Sheet").Activate
    Sheets("Purchase Order", "Contiuation Sheet").Protect , UserInterfaceOnly:=True
End Sub
 
Sub PrintInvoice()
Sheets("Purchase Order").PrintOut Copies:=1

If MsgBox("Is there a continuation sheet?", _
    vbYesNo + vbQuestion, "Confirmation") = vbNo Then
        FillSalesList
        Copy_Save
        NewInvoice
        
Else
    Sheets("Continuation Sheet").PrintOut Copies:=1
    FillSalesList1
    NewInvoice
    Copy_Save
    AllNew
   
End If
    With Sheets("Purchase Order").Unprotect
[K3] = [K3] + 1
Sheets("Purchase Order").Protect
End With
End Sub

 Private Sub FillSalesList()
    With Sheets("Sales").Columns(1).Rows(65536).End(xlUp)
        .Offset(1, 0) = Sheet1.[K3]
        .Offset(1, 1) = Sheet1.[I9]
        .Offset(1, 2) = Sheet1.[B9]
        .Offset(1, 3) = Sheet1.[K43]
        .Offset(1, 4) = Sheet1.[K44]
        .Offset(1, 5) = Sheet1.[K45]
        .Offset(1, 6) = Sheet1.[K1].Text
    End With
End Sub
 'This saves details of the invoice on another sheet
Private Sub FillSalesList1()
    With Sheets("Sales").Columns(1).Rows(65536).End(xlUp)
        .Offset(1, 0) = Sheet1.[K3]
        .Offset(1, 1) = Sheet1.[I9]
        .Offset(1, 2) = Sheet1.[B9]
        .Offset(1, 3) = Sheet7.[K54]
        .Offset(1, 4) = Sheet7.[K55]
        .Offset(1, 5) = Sheet7.[K56]
        .Offset(1, 6) = Sheet1.[K1].Text
    End With
End Sub
 
 'Clears the invoice sheet
Sub NewInvoice()

    With Sheet1
        .Unprotect
        Cells.Locked = False
        [A19:J19, I1:K3, I43:J45, I50:I54, B50:B54, B10:B14, K20:K41].Locked = True
         'Clear details of last sale
        [A20:J41, I9, B9, B49, I49].ClearContents
        [B9].Select
 
        .Protect
        End With
        
  End Sub
  
Sub AllNew()
    With Sheet7
        .Unprotect
        .Cells.Locked = False
        .Range("A13:J13, J14:J53, I54:J56, I1:K3, K14:K53").Locked = True
        .Range("A14:J53").ClearContents
        .Protect
    End With
End Sub

     
Sub Copy_Save()
 


Dim myFileName
    
Sheets(Array("Purchase Order", "Continuation Sheet")).Copy

myFileName = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")

If myFileName = False Then

    MsgBox "Save cancelled", vbCritical
    Exit Sub
    
End If

    ActiveWorkbook.SaveAs myFileName

    ActiveWorkbook.Protect
    ActiveWorkbook.Close
    


End Sub


Sub CopyAModule(wkbFrom As String, wkbTo As String, strFromMod As String)
    Dim wkb As Workbook
    Dim strFile As String
    
    Set wkb = Workbooks(wkbFrom)
    
    strFile = wkb.Path & "\vbCode.bas"
    wkb.VBProject.VBComponents(strFromMod).Export strFile
    
    On Error Resume Next
    Set wkb = Workbooks(wkbTo)
    If Err.Number <> 0 Then
        Workbooks.Open wkbTo
        Set wkb = Workbooks(wkbTo)
    End If
    
    wkb.VBProject.VBComponents.Import strFile
    wkb.Save
    
    Set wkb = Nothing
End Sub

The module 2 which I want to export is:

Code:

Private Sub Workbook_Open()
    Sheets("Purchase Order", "Contiuation Sheet").Activate
    Sheets("Purchase Order", "Contiuation Sheet").Protect , UserInterfaceOnly:=True
End Sub
 
Sub PrintInvoice()
Sheets("Purchase Order").PrintOut Copies:=1

If MsgBox("Is there a continuation sheet?", _
    vbYesNo + vbQuestion, "Confirmation") = vbNo Then
        FillSalesList
        Copy_Save
        NewInvoice
        
Else
    Sheets("Continuation Sheet").PrintOut Copies:=1
    FillSalesList1
    NewInvoice
    Copy_Save
    AllNew
   
End If
    With Sheets("Purchase Order").Unprotect
[K3] = [K3] + 1
Sheets("Purchase Order").Protect
End With
End Sub

 Private Sub FillSalesList()
    With Sheets("Sales").Columns(1).Rows(65536).End(xlUp)
        .Offset(1, 0) = Sheet1.[K3]
        .Offset(1, 1) = Sheet1.[I9]
        .Offset(1, 2) = Sheet1.[B9]
        .Offset(1, 3) = Sheet1.[K43]
        .Offset(1, 4) = Sheet1.[K44]
        .Offset(1, 5) = Sheet1.[K45]
        .Offset(1, 6) = Sheet1.[K1].Text
    End With
End Sub
 'This saves details of the invoice on another sheet
Private Sub FillSalesList1()
    With Sheets("Sales").Columns(1).Rows(65536).End(xlUp)
        .Offset(1, 0) = Sheet1.[K3]
        .Offset(1, 1) = Sheet1.[I9]
        .Offset(1, 2) = Sheet1.[B9]
        .Offset(1, 3) = Sheet7.[K54]
        .Offset(1, 4) = Sheet7.[K55]
        .Offset(1, 5) = Sheet7.[K56]
        .Offset(1, 6) = Sheet1.[K1].Text
    End With
End Sub
 
 'Clears the invoice sheet
Sub NewInvoice()

    With Sheet1
        .Unprotect
        Cells.Locked = False
        [A19:J19, I1:K3, I43:J45, I50:I54, B50:B54, B10:B14, K20:K41].Locked = True
         'Clear details of last sale
        [A20:J41, I9, B9, B49, I49].ClearContents
        [B9].Select
 
        .Protect
        End With
        
  End Sub
  
Sub AllNew()
    With Sheet7
        .Unprotect
        .Cells.Locked = False
        .Range("A13:J13, J14:J53, I54:J56, I1:K3, K14:K53").Locked = True
        .Range("A14:J53").ClearContents
        .Protect
    End With
End Sub

     
Sub Copy_Save()
 


Dim myFileName
    
Sheets(Array("Purchase Order", "Continuation Sheet")).Copy

myFileName = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")

If myFileName = False Then

    MsgBox "Save cancelled", vbCritical
    Exit Sub
    
End If

    ActiveWorkbook.SaveAs myFileName

    ActiveWorkbook.Protect
    ActiveWorkbook.Close
    


End Sub


Sub CopyAModule(wkbFrom As String, wkbTo As String, strFromMod As String)
    Dim wkb As Workbook
    Dim strFile As String
    
    Set wkb = Workbooks(wkbFrom)
    
    strFile = wkb.Path & "\vbCode.bas"
    wkb.VBProject.VBComponents(strFromMod).Export strFile
    
    On Error Resume Next
    Set wkb = Workbooks(wkbTo)
    If Err.Number <> 0 Then
        Workbooks.Open wkbTo
        Set wkb = Workbooks(wkbTo)
    End If
    
    wkb.VBProject.VBComponents.Import strFile
    wkb.Save
    
    Set wkb = Nothing
End Sub

Can this work with some tweeking?

Thanks

**pike** · 01-27-2010, 07:55 AM

you need to set the variables and run call the sub

Code:

Call CopyAModule("Your work book from.xls", "Your work book to.xls", "Mod" )

eg

Code:

Call CopyAModule("Book1.xls", Book2.xls", "Module2")

**Domski** · 01-27-2010, 08:21 AM

Still persevering with your little project I see.

Pike's suggestion should work fine but a more basic solution would be:

Code:

Sub Transfer_Module()

Dim FileName As String

FileName = "C:\Module2.bas"

ThisWorkbook.VBProject.VBComponents("Module2").Export FileName

ActiveWorkbook.VBProject.VBComponents.Import FileName
    
Kill FileName

End Sub

You could just call that after you have copied the sheets to the new workbook.

Dom

**Libster78** · 01-27-2010, 08:32 AM

Struggling with my little project but determined to make it work.

The issues i have is I cannot name the new workbook as it is an automatically created name i.e, Book2, Book3, so it is ever changing.

I'll give these a try first.

I'll get there with all your help.

Thanks guys

Libby

**Domski** · 01-27-2010, 08:41 AM

The code I posted should get round that as it transfers the module from the workbook that you are running the code (ThisWorkbook) from and the ActiveWorkbook should always be the one that is created when the sheets are copied to the new workbook.

Edit: not tested but you could probably use Pike's function like this to get round that as well:

Code:

Call CopyAModule(ThisWorkbook.Name, ActiveWorkbook.Name, "Module2")

Dom

**Libster78** · 01-27-2010, 08:48 AM

Ok that is working only my print button is automatically assigned to macro in module 1 and not to the macro in module 2 in the new sheet.

I.e. on original sheet PrintInvoice is the macro assigned to the button. The new sheet is created with module2 transferred but when print button is clicked it errors.

Now I need to automatically reassign the macro to this button.

I know I am a pain. I really appreciate all the help.

Thanks

**Domski** · 01-27-2010, 10:17 AM

You can assign the macro to run to a button like this:

Code:

Sheets("Sheet1").Buttons("Button 1").OnAction = "Your_macro_name"

Dom

**Libster78** · 01-28-2010, 06:48 AM

Thanks to all again for all the help

I habe new issues but will post these separately.

Thanks again.

Libby

Thread: Macro to export and import a module and assigning different macro automatically

Thread Tools

Display

Macro to export and import a module and assigning different macro automatically

Re: Macro to export and import a module

Re: Macro to export and import a module

Re: Macro to export and import a module

Re: Macro to export and import a module

Re: Macro to export and import a module

Re: Macro to export and import a module

Re: Macro to export and import a module

Re: Macro to export and import a module

Re: Macro to export and import a module and assigning different macro automatically

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions