Copying VBA code from one workbook into another

[gaminggod]

Hi guys. I was wondering if anyone could help me out with a problem I am having. I'm not even sure if it's possible to do this but here's my situation:

I have an existing Excel workbook with VBA code attached to the spreadsheets in it. I also have a button on the spreadsheet that when clicked generates an entirely new workbook file.

What I was wondering is if it is possible to copy the VBA code that is in the existing workbook file into the newly generated workbook file that is generated when the button is clicked? Basically when the button is clicked, I want the new workbook file to be created and have it automatically contain the code from the previous workbook. Is this even possible? I appreciate any help I can get. Thanks.

[sweet_dreams]

maybe you should think about creating workbook template. while creating a new workbook based onm this template you will have all macros, formulas and so on which exist in your template.

regards,
sweet_dreams

[RobDog888]

You can read vba macro code from within vba or from outside using vb6 or .net.
To basically read it using vba...

Application.VBE.ActiveVBProject.VBE.CodePanes.Item(1).CodeModule.Lines(1, 1)

[billhuard]

if your code is in a module you can use this

Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
    TargetWB As Workbook)
' copies a module from one workbook to another
' example: 
' CopyModule Workbooks("Book1.xls"), "Module1", Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
    strFolder = SourceWB.Path
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"
    On Error Resume Next
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
    On Error GoTo 0
End Sub

and if your code is in the sheet.
Simply copy the sheet to the new workbook and the code will follow

[billhuard]

also in order to use VbComponent from my example above, you have to check on box

Tools --> Macro --> Security --> Trusted Sources
and check the Trust Acces to Visual Basic Project

[RobDog888]

I also have a CodeBank thread on this - http://www.vbforums.com/showthread.php?t=313861

Good idae of exporting/importing the module. The only thing would be if there is a userform.

Hmm...

[billhuard]

According to Microsoft, it is possible to export/import userform too.

Here is the link http://support.microsoft.com/?kbid=292037 in the Import and Export VBComponents section

[RobDog888]

Thanks, I never had the need to export a userform, but my codebank example iterated through all vba objects. Guess I should have tested that part too and I would have seen it work.