Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

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

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  5. #5
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programmatic Macro Generation (Word and Excel

    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

  6. #6
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programmatic Macro Generation (Word and Excel

    Works bootiful. Thanks very much.

    matt

  7. #7
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •