Auto insert a vb module to a new workbook -riv

[Rivers]

Hi all me again lol

im looking everywhere for this but cant find it anywhere so was wondering if
it was possible.

i have a workbook that gets used every week when im done filling in
information i have a macro that copies the first three worksheets puts them
into a new workbook then attaches the new workbook onto an email this is fine
it works perfectly.

however im trying to create an auto open for the new workbook i tried
installing the auto open on the worksheet itself but this does not work was
wondering if i could auto install a module from the folder location and then
save the workbook.

is this possible?

rivers

 

[Barb Reinhardt]

You may be able to find something here

http://www.cpearson.com/excel/vbe.aspx

 

[Dave Peterson]

You could create a template workbook that already has that auto_open procedure
in it--Keep a single sheet with a unique name so you can delete that sheet
before you send.

Then use this template to create a new workbook
Copy the 3 sheets to the new workbook
delete the single sheet
save and send the new workbook.

Dim NewWkbk as workbook
dim actwkbk as workbook
set actwkbk = activeworkbook
set newwkbk = workbooks.add(template:="C:\yourpath\newbook.xlt")

actwkbk.worksheets(array("sheet1","sheet2","sheet3").copy _
after:=newwkbk.worksheets(1)

newwkbk.worksheets("deletemelater").delete

newwkbk.saveas ....

=====
Untested, uncompiled. Watch for typos.

And one of the nice things about using the template is that you can protect the
project that contains that auto_open procedure (if that's important). And you
don't have to worry about any security settings that would stop you from using
code to make changes to code.

 

[Rivers]

THANK YOU but have decided due to the antivirus software and having to
recreate a massive program to use the template version i going to have to
carry on looking

i wasnt looking for the VB editor to write vb i just wanted the editor to
insert a module i had saved in the folder as a .baa file

any further ideas any one?

ps thanks you dave and barb knowing that bit of info i will definatly use in
future packs.

 

[Rivers]

sorry not .baa file .bas file

 

[Dave Peterson]

I don't understand what any antivirus software would do to prevent you from
using a template.

Start a new workbook
rename the first worksheet and delete all the others
import this .bas file into the workbook's project
save this file as a template and you're done.

Or you could look at the link that Barb suggested and review the code labeled:
Copy A Module From One Project To Another