Inserting Module into Workbook

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

Hi,
I have an application that creates a summary workbook on the fly, every time
the uses presses the 'summary build' button the workbook is overwritten.

I would like to be able to include in the summary workbook some macros that
can be used in isolation of the application.

What is the best route to achieving this?

I had thought of creating the module in the application and somehow copying
this into the summary workbook, but I am not sure what the code is to do
this.

TIA
Nigel
 
I'm "reposting" this information. Something similar came up "yesterday".. If you look at the threads that started 12/28/2003 and look for Bloodhound.ExcelMacro as the title, you can follow a thread that discusses "copying" the VB code you wish to copy... Yes I am simply copying the "last" post, without modifications, hopefully you can work out the details from what is posted. See the bottom of this message for the previously posted text that is described above.
----- Nigel wrote: -----

Hi,
I have an application that creates a summary workbook on the fly, every time
the uses presses the 'summary build' button the workbook is overwritten.

I would like to be able to include in the summary workbook some macros that
can be used in isolation of the application.

What is the best route to achieving this?

I had thought of creating the module in the application and somehow copying
this into the summary workbook, but I am not sure what the code is to do
this.

TIA
Nigel







typo, that should be:

Sub CopyMod()
Dim Fname As String
With Workbooks("VBA Code Examples.xls")
Fname = .Path & "\code.txt"
.VBProject.VBComponents("Module2").Export Fname
End With
Application.Run "imprt", Fname
End Sub

Function imprt(ByVal strfile As String)
Workbooks("book2.xls").VBProject.VBComponents.Import strfile
End Function
 
As for best route, (Sorry didn't answer in my previous response) in my limited experience of copying code from one workbook to another, I would have the code obviously already written in your "source" workbook. Then when the summary workbook is created, to copy the code over to the summary workbook where/however you would like it.

One thing I'm not sure about, is if you can use the method that was described to copy the code for ThisWorkbook...

I had personnally found a different way around this in a program I wrote a few years ago, but it was/is rather complicated... Probably I made it more so than was necessary, but it worked. :)
 
You could export the module to a text file, then import it to the new workbook
(or just keep the text file available all the time and just import it when you
need it).

But even simpler may be to create a template file that contains all your code
(and maybe all the formatting that you need).

Then just use that to create the summary workbook.

You could even just add a Open the template and move the worksheets to that
template if you don't want to modify your existing code too much.

(And the nice thing about this is you can keep the template's project locked so
no one can modify your code.)
 
Many thanks, templates seems to be the best route. I'm trying to set
something up now.

Cheers
Nigel
 
Back
Top