PRJ: Using DDEExecute to Pass Arguments to MS Project Macros

MORE INFORMATION

The Visual Basic, Applications Edition, Macro method does not accept arguments. Therefore, a non-project macro cannot use OLE Automation to run a Microsoft Project macro that requires arguments.

However, some applications support DDE commands that can be used to send a command string to another application that also supports DDE. For example, Microsoft Word, Microsoft Excel, Microsoft Project, and Microsoft Access supply the DDEExecute command, and Microsoft Visual Basic has the LinkExecute method. These DDE commands, or an equivalent DDE command, can be used to run a Microsoft Project macro that requires arguments.

NOTE: The only macros that can send parameters via DDE are macros located in the active macro library or in a library referenced by the active macro library. The active macro library is GLOBAL.MPT or the active project (if it contains macros). When a project is first opened or created, the active macro library is GLOBAL.MPT.

Example 1

Assume that Microsoft Project is running, and that all macros in the current session are in GLOBAL.MPT and each macro has a distinct name. The Microsoft Excel macro, ExcelMacro1, passes the string "Plumbing" and the long integer 48000 to the Microsoft Project macro, ProjectMacro1. ProjectMacro1 uses these arguments to add a new task called "Plumbing" with a duration of 48000 minutes.

'This macro is in a Microsoft Project module in GLOBAL.MPT.
'It is called from the Microsoft Excel macro below.

Sub ProjectMacro1(sName As String, longMinutes As Long)

    'declare variables
    Dim T As Object

    'create a new task and set the name based on passed parameter "sName"
    Set T = ActiveProject.Tasks.Add (Name:=sName)

    'set new task's duration equal to passed parameter "longMinutes"
    T.Duration = longMinutes

End Sub

'This macro is in a Microsoft Excel module; when you run it,
'it calls the above Microsoft Project macro procedure.

Sub ExcelMacro1()

    'declare variables
    Dim channel As Integer, command As String

    'establish a DDE channel to Microsoft Project's system topic
    channel = DDEInitiate("winproj","system")

    'create a command string
    'double quotes are used to quote an item within a quoted string
    command = "ProjectMacro1 ""Plumbing"", 48000"

    'send command string to Microsoft Project
    DDEExecute channel, command

    'terminate DDE channel to Microsoft Project
    DDETerminate channel

End Sub
				

The DDE command string that Microsoft Project sees is:

Example 2

This is the same as the above example, except that the Microsoft Excel macro, ExcelMacro2, passes the values stored in cells A1 and B1 of the active sheet to the above procedure "ProjectMacro1". The string "Plumbing" is in cell A1 and the long integer 48000 is in cell B1.

'This macro is in an Excel module; when run,
'it calls the above Microsoft Project macro.
'The worksheet containing this data must be
'active when the macro is run.

Sub ExcelMacro2()

    'declare variables
    Dim channel As Integer, command As String
    Dim stringX As String, longY As Long

    'store values in spreadsheet cells into variables
    stringX = Range("A1")
    longY = Range("B1")

    channel = DDEInitiate("winproj","system")

    'Chr(34) is the quote character.  It is used to concatenate
    'quotes around the stringX variable in the DDE command string.

    command = "ProjectMacro1 " & Chr(34) & stringX & Chr(34) & _
               ", " & longY

    'The quote character could have been incorporated directly,
    'without the Chr function, as follows:

    'command = "ProjectMacro1 """ & stringX & """, " & longY

    DDEExecute channel, command
    DDETerminate channel

End Sub

				

The DDE command string that Microsoft Project sees is: