Writing Macros To A New Workbook 1

[simoncpage]

How can I write macro to a newly created workbook from an add-in.

I have an add-in that create a new workbook and all the neccessary text, formating etc...But I want to add functionality to the new workbook when it is created how can I do this. Ideally I want to copy a module straight from the add-in?

Any help would be appreciated?

 

[rmikesmith]

Simon,


Try using something like the following:

Sub AddModule()

Dim FileName As String
Dim VBP As Object 'VBProject

   FileName = ThisWorkbook.Path & "\TempModule.bas"   'Select a name not likely to exist
   ThisWorkbook.VBProject.VBComponents("ModuleToAdd").Export FileName
   Set VBP = ActiveWorkbook.VBProject  'Or use specific workbook ref
   VBP.VBComponents.Import FileName
'  Delete the temorary module file
   Kill FileName
 
End Sub

Notes: ModuleToAdd should contain all procedures/functions you want in the new workbook and must exist in your addin. You'll probably want to add error handling!

Regards,
M. Smith