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

[hearthhrob4gals]

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

 

[hearthhrob4gals]

i had one more query on the below line:


MsgBox "Your security settings do not allow this macro to run."

if user gets this pop-up then whats the best mesgbox to put for guiding him on the next step. i dont want him to google or reasech as what he needs to do in settings.

Thanks

 

[Domenic]

Or, let's say that you want to copy 10 lines of code, starting with Line 2...

    [COLOR=darkblue]With[/COLOR] vbcSource.CodeModule
        StartLine = 2
        strCode = .Lines(StartLine, 10) [COLOR=#006400]'strCode = .Lines(StartLine, .CountOfLines - 3)
[/COLOR]    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] vbcDest.CodeModule
        NextLine = .CreateEventProc("Open", "Workbook")
        NextLine = NextLine + 1
        .InsertLines NextLine, strCode
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

As for the message regarding the security setting, accessing it varies slightly depending on the version of Excel. So you may want to simply include written instructions for each of the versions, along with a copy of the code.

 

[hearthhrob4gals]

Thanks

 

[hiker95]

Domenic,

Thank you so much - one for my archives.

 

[Domenic]

Cheers to both of you!

 

[hearthhrob4gals]

One more doubt

how can we check if there is already a code in that section... i want the above code to be pasted only if the workbook_open is blank.

Thanks

 

[Domenic]

Try...

[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]Dim[/COLOR] bCopied             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/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 v2.xlsm")
    
    [COLOR=darkblue]Set[/COLOR] vbcSource = wkbSource.VBProject.VBComponents("Module1")
    [COLOR=darkblue]Set[/COLOR] vbcDest = wkbDest.VBProject.VBComponents("ThisWorkbook")
    
    bCopied = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]With[/COLOR] vbcDest.CodeModule
        [COLOR=darkblue]If[/COLOR] .CountOfLines = 0 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]With[/COLOR] vbcSource.CodeModule
                StartLine = 2
                strCode = .Lines(StartLine, 10)
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            NextLine = .CreateEventProc("Open", "Workbook")
            NextLine = NextLine + 1
            .InsertLines NextLine, strCode
            bCopied = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] bCopied [COLOR=darkblue]Then[/COLOR]
        MsgBox "Completed...", vbExclamation
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "Code already exists!", vbExclamation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [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]

Also, as you know, the macro copies 10 lines of code, starting at line 2. Instead of specifying the number of lines as 10, we can specify the number of lines as "the number of lines in the procedure found at line 2". To do so, try replacing...

            [COLOR=darkblue]With[/COLOR] vbcSource.CodeModule
                StartLine = 2
                strCode = .Lines(StartLine, 10)
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

with

            [COLOR=darkblue]With[/COLOR] vbcSource.CodeModule
                StartLine = 2
                strCode = .Lines(StartLine, .ProcCountLines(.ProcOfLine(StartLine, 0), 0))
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Hope this helps!