exporting module-code from one workbook to workbook open section of another workbook

H

hearthhrob4gals

Board Regular
Joined
Aug 20, 2014
Messages
142
Hi,

I want to export 6-7 lines of code(written in a module) to another workbook. i know the code for exporting module from one excel file to another. however the cgalnnge here is that the code from module from the first workbook should be exported and saved in the workbook_open of the another workbook.

Thanks
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
hi,

what you sent is useful only for exporting a module to module. but i want to export code in a module to 'thisworkbook' and in that workbook-open should be selected
 
Upvote 0
hearthhrob4gals,

what you sent is useful only for exporting a module to module. but i want to export code in a module to 'thisworkbook' and in that workbook-open should be selected
Click to expand...

That is correct.

I attempted to solve your request, but, I was not able to.

I also sent a Private Message to Domenic, MrExcel MVP, to help in solving your request.

You should follow-up with Domenic.
 
Upvote 0
The following macro assumes that both the source and destination workbooks are open, and copies the code from "Module1" in the source workbook to the code module for "ThisWorkbook" in the destination workbook...

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] ImportCode()

    [COLOR=darkblue]Dim[/COLOR] wkbSource           [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] wkbDest             [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] VBP                 [COLOR=darkblue]As[/COLOR] VBProject
    [COLOR=darkblue]Dim[/COLOR] vbcSource           [COLOR=darkblue]As[/COLOR] VBComponent
    [COLOR=darkblue]Dim[/COLOR] vbcDest             [COLOR=darkblue]As[/COLOR] VBComponent
    [COLOR=darkblue]Dim[/COLOR] strCode             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] StartLine           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] NextLine            [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] VBP = ActiveWorkbook.VBProject
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    [COLOR=darkblue]If[/COLOR] VBP [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        MsgBox "Your security settings do not allow this macro to run."
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] wkbSource = Workbooks("Book1.xlsm")
    [COLOR=darkblue]Set[/COLOR] wkbDest = Workbooks("Book2.xlsm")
    
    [COLOR=darkblue]Set[/COLOR] vbcSource = wkbSource.VBProject.VBComponents("Module1")
    [COLOR=darkblue]Set[/COLOR] vbcDest = wkbDest.VBProject.VBComponents("ThisWorkbook")
    
    [COLOR=darkblue]With[/COLOR] vbcSource.CodeModule
        StartLine = 1
        [COLOR=green]'StartLine = .CountOfDeclarationLines + 1[/COLOR]
        strCode = .Lines(StartLine, .CountOfLines)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] vbcDest.CodeModule
        NextLine = .CountOfLines + 1
        .InsertLines NextLine, strCode
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    MsgBox "Completed...", vbExclamation
    
    [COLOR=darkblue]Set[/COLOR] wkbSource = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wkbDest = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] VBP = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] vbcSource = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] vbcDest = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!
 
Upvote 0
Hi,

Thanks for your prompt reply. i am getting error at this line:
Dim VBP As VBProject 'stating user-defined type not defined'
 
Upvote 0
Sorry, I initially used early binding with the intention of changing it to late binding. So try the following instead...

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] ImportCode()

    [COLOR=darkblue]Dim[/COLOR] wkbSource           [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] wkbDest             [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] VBP                 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vbcSource           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vbcDest             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strCode             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] StartLine           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] NextLine            [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] VBP = ActiveWorkbook.VBProject
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    [COLOR=darkblue]If[/COLOR] VBP [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        MsgBox "Your security settings do not allow this macro to run."
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] wkbSource = Workbooks("Book1.xlsm")
    [COLOR=darkblue]Set[/COLOR] wkbDest = Workbooks("Book2.xlsm")
    
    [COLOR=darkblue]Set[/COLOR] vbcSource = wkbSource.VBProject.VBComponents("Module1")
    [COLOR=darkblue]Set[/COLOR] vbcDest = wkbDest.VBProject.VBComponents("ThisWorkbook")
    
    [COLOR=darkblue]With[/COLOR] vbcSource.CodeModule
        StartLine = 1
        [COLOR=green]'StartLine = .CountOfDeclarationLines + 1[/COLOR]
        strCode = .Lines(StartLine, .CountOfLines)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] vbcDest.CodeModule
        NextLine = .CountOfLines + 1
        .InsertLines NextLine, strCode
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    MsgBox "Completed...", vbExclamation
    
    [COLOR=darkblue]Set[/COLOR] wkbSource = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wkbDest = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] VBP = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] vbcSource = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] vbcDest = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!
 
Upvote 0
Thanks Domenic..

since i wanted the module code to be pasted under workbook_open, i did the following:


With vbcSource.CodeModule StartLine = 2 (changed it to 2 so gthat it doesnt copy the macro name) 'StartLine = .CountOfDeclarationLines + 1 strCode = .Lines(StartLine, .CountOfLines) End With
</pre>
With vbcDest.CodeModule
NextLine = .CreateEventProc("Open", "Workbook") (added this line)
NextLine = .CountOfLines + 1
.InsertLines NextLine, strCode
End With



At the end had to delete one end sub, as the module code was getting pasted below the workbook_open.

With vbcDest.CodeModule
.deleteLines 3
End With

i am not sure if the best way to do it, but that came in my mind n fixed my problem. thanks a ton for your assistance.
 
Upvote 0
hearthhrob4gals,

Could you please post all of your final macro code?


When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]
 
Upvote 0
Below is the complete code:

Code: 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
  MsgBox "Browse for the dashboard"
    Pfilepath = Application.GetOpenFilename("All(*.*),*.*", 3, "Select the file to open")
    If Pfilepath = "False" Then
    Exit Sub
    End If
    pFilename = Mid$(Pfilepath, InStrRev(Pfilepath, "\") + 1)
    Workbooks.Open Filename:=Pfilepath
    Windows(pFilename).Activate



Dim wkbSource           As Workbook
    Dim wkbDest             As Workbook
    Dim VBP                 As Object
    Dim vbcSource           As Object
    Dim vbcDest             As Object
    Dim strCode             As String
    Dim StartLine           As Long
    Dim NextLine            As Long
    
    On Error Resume Next
    Set VBP = ActiveWorkbook.VBProject
    On Error GoTo 0
    If VBP Is Nothing Then
        MsgBox "Your security settings do not allow this macro to run."
        Exit Sub
    End If
    
    Set wkbSource = Workbooks("Book1.xlsm")
    Set wkbDest = Workbooks(pFilename)
    
      Set vbcSource = wkbSource.VBProject.VBComponents("Module5")
    Set vbcDest = wkbDest.VBProject.VBComponents("ThisWorkbook")
    
    With vbcSource.CodeModule
        StartLine = 2
        strCode = .Lines(StartLine, .CountOfLines)
    End With
    
    With vbcDest.CodeModule
         NextLine = .CreateEventProc("Open", "Workbook")
        NextLine = .CountOfLines + 1
        .InsertLines NextLine, strCode
    End With
    
       With vbcDest.CodeModule
        .deleteLines 3
    End With
    
    
    MsgBox "Completed...", vbExclamation
    
    Set wkbSource = Nothing
    Set wkbDest = Nothing
    Set VBP = Nothing
    Set vbcSource = Nothing
    Set vbcDest = Nothing
    
End Sub
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Excel Crashing after SaveAs
Replies
0
Views
193
ampd
A
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
223
Insurance Dave
I
gallen
  • Question
Macro Enabled Workbooks Suddenly Corrupting Regularly.
Replies
5
Views
985
Joe4
Joe4
B
  • Question
Copy Paste from one Workbook to another
Replies
1
Views
217
rollis13
rollis13
C
  • Question
Run vba on dataset using criteria and input data from another dataset?
Replies
0
Views
79
CaraM
C

Forum statistics

Threads
1,214,919
Messages
6,122,260
Members
449,075
Latest member
staticfluids

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