Importing a module via VBA?

[DCSwearingen]

Background:
I modified code Dave Peterson posted a couple of months ago:
http://www.excelforum.com/showpost.p...89&postcount=7

I am using this to create monthly workbooks for several purposes, one of which is to convert daily cost sheets into a more useful database format.

I copy and paste the values from the daily cost sheet into the tab corresponding to the cost sheet date. The formatting of these is such that they cannot be used as they are as a form of database and I have no control over the format being sent to me.

So I have added three additional worksheets, one to convert the daily info into a daily database, one to do the same as a MTD database, and one to convert to a YTD database.

I have recorded and edited macros to place the correct formulas into my three databases.

My Question:
I have looked through the VBA Help files in every fashion I can think of as to automatically importing this code when I create the new workbooks, but I cannot find how to automatically import code.

I tried recording a new macro while I imported the *.bas file, but the resulting macro was blank.

Is there a way to copy or import my code with VBA? Or will I need to continue to manually import the code after each new workbook is created?

[jtp]

Here is one way:

Sub CopyModule()
Dim ModFile As String
Dim myVBP As VBProject

ModFile = ThisWorkbook.Path & "\myMod.bas"
ThisWorkbook.VBProject.VBComponents("Module1").Export ModFile

Set myVBP = Workbooks("DestinationWB").VBProject
myVBP.VBComponents.Import ModFile

Kill ModFile
End Sub

Make sure you add the Microsoft Visual Basic For Applications Extensibility Library before running. You may also get a security warning. If so, goto Tools-->Macro-->Security-->Trusted Sources Tab and Select Trust Access To Visual Basic Project.

HTH

Jason

[DCSwearingen]

I don't know if I will get time to work with this yet today, but Thank You for the input.

I will try it out ASAP...