none
Updating a Macro via VBA RRS feed

  • Question

  • Does anyone know a way for one Project VBA macro to update the code in another Project VBA macro?
    Saturday, November 20, 2010 7:21 PM

Answers

  • Thanks, everyone, for the input. I've attached code for one macro to modify another macro. There are a few points to keep in mind:
    - You need to set References for the code to work. See below.
    - I found some excellent material on the Web and have added the sources at the end.
    - In real life, you need to add edits and error handling to prevent mishaps (see Hans' post).

    Here is the code:

     Sub McModMc()

    'Ensure Object Library References are set

    'Set: Tools/References/Microsoft Visual Basic for Applications Extensibility 5.3

    'Set: Tools/References/Microsoft Word 11.0 Object Library

    'Restart Project for References to take effect

     

    'Export the target macro to a file. Note: code assumes file does not already exist.

    VBE.VBProjects(1).VBComponents("Module1").Export FileName:="F:\ExportTest.bas"

     

    'Prepare to edit using Word

    'Start an existing instance of Word, or if Word is not running, start it

    Dim wdApp As Word.Application

    On Error Resume Next

    Set wdApp = GetObject(, "Word.Application")

    If Err <> 0 Then

        On Error Resume Next

        Set wdApp = CreateObject("Word.Application")

        If Err <> 0 Then

            MsgBox "Word application is not available." & Chr(13) & _

                "Install Word or check network connection and try again.", vbCritical

            GoTo EndLine

        End If

    End If

     

    'Put Word into the background until editing is done

    wdApp.Visible = False

    wdApp.ScreenUpdating = False

    wdApp.DisplayAlerts = False

     

    'Perform edits in Word

    wdApp.Documents.Open ("F:\ExportTest.bas")

    wdApp.Selection.Find.ClearFormatting

    wdApp.Selection.Find.Replacement.ClearFormatting

        With wdApp.Selection.Find

            .Text = "F:\"

            .Replacement.Text = "X:\"

            .Forward = True

            .Wrap = wdFindContinue

            .Format = False

            .MatchCase = False

            .MatchWholeWord = False

            .MatchWildcards = False

            .MatchSoundsLike = False

            .MatchAllWordForms = False

        End With

    wdApp.Selection.Find.Execute Replace:=wdReplaceAll

    wdApp.ActiveDocument.Save

    wdApp.ActiveDocument.Close

       

    'With editing complete, move Word into foreground

    wdApp.Visible = True

    wdApp.ScreenUpdating = True

    wdApp.DisplayAlerts = True

     

    'Import macro back into Project. Note: Code assumes it's OK to delete old module.

    OrganizerDeleteItem Type:=3, FileName:="Global.MPT", Name:="Module1"

    Set x = VBE.VBProjects(1).VBComponents.Import("F:\ExportTest.bas")

     

    EndLine:

    End Sub

     

    Source material:

    Chip Pearson, http://www.cpearson.com/excel/vbe.aspx

    Setting References programmatically: http://www.mrexcel.com/forum/showthread.php?t=391520

     

    Tuesday, November 23, 2010 4:48 PM

All replies

  • Hi Rob,

    I've been doing some tests with

    VBE.VBProjects(1).VBComponents(5).CodeModule.

    (Whereby The index 1 for components is ThisProject in the global, 2... are the other modules)

    Which has some interesting properties but I can only make the passive ones work (such as name or CountOfLines); I get an exception when I try to replace text or lines.

    Maybe from here you have some inspiration?

    Greetings, 

    Saturday, November 20, 2010 9:56 PM
    Moderator
  • Thanks, Jan. Regarding the macro as an object gives me an idea. Maybe what we need to do is export the text to a file, make the edits on that file, and then import the modified code back into the macro object.

    If your technique gives us access to the CodeModule text, we can export it from the macro with the Export command. It is exported as a .bas file, but that's OK.  A .bas file can be manually updated in Word. I have accessed Excel from within a Project VBA macro many times, but I haven't done so with Word. If we can access Word as we do Excel, maybe we can open the .bas file, apply edits, save it, and then use the Import command to bring the modified code back into the macro object.

    What do you think?

    Sunday, November 21, 2010 3:41 PM
  • Jan, most of the solution outlined in my last post is now working. Once I've finished testing, I'll post the result. 
    Sunday, November 21, 2010 11:27 PM
  • Hi Rob,

    Keep in mind that all loaded macros cannot be updated using code. So make sure that your update code is not referring to or using the module or form that you are trying to update. This is typically the case if the module has some global variables defined that you use in other modules, or the module is an error handling module, etc.

    Another solution you can consider, is to create a new module containing the updated code, and use the organizer to replace the old module with the new module.

    But once again, every reference to the module or element in the module (sub, constant, ...) can cause this update to fail.

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    Tuesday, November 23, 2010 9:48 AM
    Moderator
  • Thanks, everyone, for the input. I've attached code for one macro to modify another macro. There are a few points to keep in mind:
    - You need to set References for the code to work. See below.
    - I found some excellent material on the Web and have added the sources at the end.
    - In real life, you need to add edits and error handling to prevent mishaps (see Hans' post).

    Here is the code:

     Sub McModMc()

    'Ensure Object Library References are set

    'Set: Tools/References/Microsoft Visual Basic for Applications Extensibility 5.3

    'Set: Tools/References/Microsoft Word 11.0 Object Library

    'Restart Project for References to take effect

     

    'Export the target macro to a file. Note: code assumes file does not already exist.

    VBE.VBProjects(1).VBComponents("Module1").Export FileName:="F:\ExportTest.bas"

     

    'Prepare to edit using Word

    'Start an existing instance of Word, or if Word is not running, start it

    Dim wdApp As Word.Application

    On Error Resume Next

    Set wdApp = GetObject(, "Word.Application")

    If Err <> 0 Then

        On Error Resume Next

        Set wdApp = CreateObject("Word.Application")

        If Err <> 0 Then

            MsgBox "Word application is not available." & Chr(13) & _

                "Install Word or check network connection and try again.", vbCritical

            GoTo EndLine

        End If

    End If

     

    'Put Word into the background until editing is done

    wdApp.Visible = False

    wdApp.ScreenUpdating = False

    wdApp.DisplayAlerts = False

     

    'Perform edits in Word

    wdApp.Documents.Open ("F:\ExportTest.bas")

    wdApp.Selection.Find.ClearFormatting

    wdApp.Selection.Find.Replacement.ClearFormatting

        With wdApp.Selection.Find

            .Text = "F:\"

            .Replacement.Text = "X:\"

            .Forward = True

            .Wrap = wdFindContinue

            .Format = False

            .MatchCase = False

            .MatchWholeWord = False

            .MatchWildcards = False

            .MatchSoundsLike = False

            .MatchAllWordForms = False

        End With

    wdApp.Selection.Find.Execute Replace:=wdReplaceAll

    wdApp.ActiveDocument.Save

    wdApp.ActiveDocument.Close

       

    'With editing complete, move Word into foreground

    wdApp.Visible = True

    wdApp.ScreenUpdating = True

    wdApp.DisplayAlerts = True

     

    'Import macro back into Project. Note: Code assumes it's OK to delete old module.

    OrganizerDeleteItem Type:=3, FileName:="Global.MPT", Name:="Module1"

    Set x = VBE.VBProjects(1).VBComponents.Import("F:\ExportTest.bas")

     

    EndLine:

    End Sub

     

    Source material:

    Chip Pearson, http://www.cpearson.com/excel/vbe.aspx

    Setting References programmatically: http://www.mrexcel.com/forum/showthread.php?t=391520

     

    Tuesday, November 23, 2010 4:48 PM
  • Interesting. Thanks for posting the solution!
    Jack Dahlgren blogs at:
    Project and Retrovention
    and rarely Twitter
    Tuesday, November 23, 2010 6:05 PM
    Moderator