Programmatic Macro Generation (Word and Excel XP (2002))

[matthewdjb]

I need a program that opens a text file containing a macro, and makes that macro part of all the Word documents in a folder. Each document needs to have the macro by itself.

Then I need the program to do the same for Excel.

I need a robust and easy to use solution.

Does anyone have such a thing. Is it possible? How simple to write? I only have VBA. Would VB be required?

Cheers

matt

[HansV]

For Word, it would be more efficient to put the macro in the template from which the documents were made, or in a global template. For Excel, it would be more efficient to put the macro in an add-in.

But if you really need to do this, you can create a macro in Word to handle the Word documents, and a macro in Excel to handle the Excel workbooks. In both cases, you must select Tools | References... in the Visual Basic Editor, and set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library.

Here is a macro that loops through all Word documents in a folder:

Sub AddMacro()
' Text file with macro.
Const strMacroFile = "C:TestMyMacro.bas"
' Path for documents - note the trailing backslash.
Const strPath = "F:Word"
Dim strFile As String
Dim doc As Document

strFile = Dir(strPath & "*.doc")
Do While Not strFile = ""
Set doc = Documents.Open(strPath & strFile)
doc.VBProject.VBComponents.Import strMacroFile
doc.Close SaveChanges:=True
strFile = Dir
Loop

Set doc = Nothing
End Sub

And here is a similar version for use in Excel:

Sub AddMacro()
' Text file with macro.
Const strMacroFile = "C:TestMyMacro.bas"
' Path for workbooks - note the trailing backslash.
Const strPath = "C:Excel"
Dim strFile As String
Dim wbk As Workbook

strFile = Dir(strPath & "*.xls")
Do While Not strFile = ""
Set wbk = Workbooks.Open(strPath & strFile)
wbk.VBProject.VBComponents.Import strMacroFile
wbk.Close SaveChanges:=True
strFile = Dir
Loop

Set wbk = Nothing
End Sub

[StuartR]

Before you can run this Word macro you may need to visit Tools > Macro > Security > Trusted Sources and check the box marked "Trust access to Visual Basic project". I strongly recommend that you clear this check box afterwards as it protects you from some types of virus.

StuartR

[HansV]

Thanks, good point. I forgot to mention that. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

[matthewdjb]

That's great, I'll give it a go. The reason I can't use a template or an addin is that each document has to be standalone.

Thanks

matt

[matthewdjb]

Works bootiful. Thanks very much.

matt

[kmurdock]

Also, Norton AntiVirus has an Office plug-in that will find macro activity like this suspicious. Even turning the plug-in off may cause a very persistent Bloodhound alarm that will prevent copying a template with code to manipulate macro projects.

I have such a template and had to tell Norton explicitly to ignore it.

K