Q

How do I create a macro that can create a file that includes another macro in it?

I currently have a macro in Excel that creates an Excel template by importing a text file into the spreadsheet and then saving it as a template. I want the template to have a macro in it that does something different. How do I create a macro that can create a file that includes a macro in it?
Interesting question. What you'd have to do is write a macro to parse up your text file and as part of that macro, write the macro you want to write. Is that recursive enough for you? What you are going to have to do is manipulate the Visual Basic Editor 6.0 Object Model to drive your macro creation. In Office XP Developer, the Visual Basic Editor 6.0 Object Model is discussed under the Microsoft Office XP Developer Object Model Guide, Shared Components section of the documentation. The ins and outs are in the VBOB6.CHM help file. When you install Office XP, make sure to check that you want all the programming help/VBA files installed so you get it installed.
This was last published in December 2001

Dig Deeper on Windows Operating System Management

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

2 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

How to create an Excel macro programmatically:

'Assuming  that XLBook has a reference to the open Workbook where you want to create the macro
Set objCodeModule = XLBook.VBProject.VBComponents.Item("Module1").CodeModule
sNewCode = "Public Function NewFunction(ByVal intStartRow, ByVal intEndRow)" & vbCrLf
sNewCode = sNewCode &" Sheet1.Range(Sheet1.Cells(intStartRow, 1), Sheet1.Cells(intEndRow, 10)).Select" & vbCrLf
sNewCode = sNewCode &" Selection.Interior.ColorIndex = 35" & vbCrLf
sNewCode = sNewCode &"End Function" & vbCrLf

objCodeModule.AddFromString(sNewCode)

'do something else but don't forget to use Save method to save the code
Cancel
Never had a need to do it, but saving the code snip to my files for future reference.

Will have to try it out, thanks.
Cancel

-ADS BY GOOGLE

SearchServerVirtualization

SearchCloudComputing

SearchExchange

SearchSQLServer

SearchWinIT

SearchEnterpriseDesktop

SearchVirtualDesktop

Close