can I use code to make code in another book
 

I want to write a macro that will make a new workbook and I want the new
workbook to have a macro in it.

Ideally, there will be a button on one of the sheets that will run it too.

is that possible?

Can I write a macro that will write a macro?

Thanks

M

can I use code to make code in another book
 

yes, but the way i normally use is to export the code module from the
existing workbook and import it into the new one.

as an example, this would export the shade_rows module, the the path i have
set in the fpath variable:

ThisWorkbook.VBProject.VBComponents("Mod_Shade_Row s").Export Filename:=fPath
& "Mod_Shade_Rows.bas"

then, after creating the new workbook, i import that module:
ActiveWorkbook.VBProject.VBComponents.Import Filename:=fPath &
"Mod_Shade_Rows.bas"

once in a while, i will create a code module, too.
this adds a workbook_open and workbook_beforeclose module, watch out for
wrapping by the forum/news reader. the long lines that wrap are all on one
line:

AddProcedureToModule()
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Thisworkbook")
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, "Private Sub Workbook_Open()"
LineNum = LineNum + 1
.InsertLines LineNum, "UserForm3.Show"
LineNum = LineNum + 1
.InsertLines LineNum, "Application.MacroOptions Macro:=" &
"""Show_UserForm3""" & ", Description:=""" & """" & ", ShortcutKey:=" &
"""F"""
LineNum = LineNum + 1
.InsertLines LineNum, "Application.MacroOptions Macro:=" &
"""Shade_Rows""" & ", Description:=""" & """" & ", ShortcutKey:=" & """P"""
LineNum = LineNum + 1
.InsertLines LineNum, "CreateMenubar3"
LineNum = LineNum + 1
.InsertLines LineNum, "End Sub"

LineNum = LineNum + 1
.InsertLines LineNum, "Private Sub Workbook_BeforeClose(Cancel
As Boolean)"
LineNum = LineNum + 1
.InsertLines LineNum, "RemoveMenubar3"
LineNum = LineNum + 1
.InsertLines LineNum, "Application.MacroOptions Macro:=" &
"""Show_UserForm3""" & ", Description:=""" & """" & ", ShortcutKey:=" &
""""""
LineNum = LineNum + 1
.InsertLines LineNum, "Application.MacroOptions Macro:=" &
"""Shade_Rows""" & ", Description:=""" & """" & ", ShortcutKey:=" & """"""
LineNum = LineNum + 1
.InsertLines LineNum, "End Sub"

End With


--


Gary Keramidas
Excel 2003


"Michelle" wrote in message
...
I want to write a macro that will make a new workbook and I want the new
workbook to have a macro in it.

Ideally, there will be a button on one of the sheets that will run it too.

is that possible?

Can I write a macro that will write a macro?

Thanks

M

can I use code to make code in another book
 

How about some alternatives?

If you're creating multiple workbooks that need the same macro, then I wouldn't
want to put copies of the same macro in all those workbooks. When (not if!),
they need to be updated, you'll never be able to find all the files that were
created (and then used to create more!).

Instead, I'd create a single addin file. This would have the macro in it and a
way to run that macro (menubar, toolbar, QAT, ribbon modifications).

When I do this kind of thing, the code can usually run against the activesheet
in any workbook. You may want to add a "are you sure" prompt -- or even check
some indicator on that sheet (a hidden name on that sheet???) before continuing.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003
and xl2007.
http://msmvps.com/blogs/xldynamic/ar...me-simple.aspx

===================
Alternative #2.

If you have to have code in the new workbooks, then I wouldn't create the code
on the fly. There's user security setting that will stop your code from running
if the user chooses not to allow this kind of code.

Instead you could create a workbook template (*.xlt or *.xltm) that contains all
the code you need. And you could also include any other common details that you
need -- page layout on each sheet, filters, event macros, ...

Then use that template file when you're creating the new workbook.

Option Explicit
Sub testme()

Dim TemplFileName As String
Dim NewWkbk As Workbook

TemplFileName = "C:\path to template\template.xlt"

Set NewWkbk = Workbooks.Add(template:=TemplFileName)

End Sub

You could even protect the template's project (in the VBE) and that may help
keep prying eyes from making (unauthorized) changes to the code.


Michelle wrote:

I want to write a macro that will make a new workbook and I want the new
workbook to have a macro in it.

Ideally, there will be a button on one of the sheets that will run it too.

is that possible?

Can I write a macro that will write a macro?

Thanks

M

--

Dave Peterson

can I use code to make code in another book
 

And if you save your workbook with save as and get to it a new name? It's
more easy to do.
Regards, Ste'

"Michelle" wrote:

I want to write a macro that will make a new workbook and I want the new
workbook to have a macro in it.

Ideally, there will be a button on one of the sheets that will run it too.

is that possible?

Can I write a macro that will write a macro?

Thanks

M