Lesson 4: DDE vs. OLE Automation

Lesson Objectives

Upon completion of this lesson, the participant will be able to:

• List some of the DDE keywords found in Excel, Project, Word, and VB3.
• Describe the purpose of DDEInitiate, DDETerminate, DDEExecute, DDEPoke and DDERequest.
• Describe the error messages that can occur if a target application does not respond quickly enough to DDE or OLE Automation commands.
• Describe how a macro in another application can run an Excel or Project macro and pass arguments.
• Describe how a macro in another application can assign values to a global variable in Project.

Some Topics to be introduced in this lesson include:

• DDE Commands Used in Excel, Project, Word, and VB3
• DDE and OLE Automation Timeouts
• Macros Running Another Application's Macros and Passing Arguments
• Macros Assigning Values to Another Application's Global Variables

DDE Macro Languages - Quick Review of a DDE Conversion

A source application has macro commands that allow it to initiate a conversation with a target application. It can choose to talk to the target application as a "whole" (the System topic) or with a specific open document. The source application can then use a DDE command to read (request) specific data from the target application or document, or to change (poke) the values of specific data in the target application or document. What request/poke items the target will respond to depends on the specific target application, and whether the source is talking to the system topic or to a specific document. The request/poke items the target will respond to are "hard coded" in the target application, and usually cannot be executed in the target's own macros. In addition to request/poke, the source application can also use DDE to tell the target to execute one of the target's own menu or macro commands - it usually doesn't matter whether the conversation is with the system topic or with a specific document.

The exact syntax and degree of DDE support depends on the application. Some applications allow simultaneous conversations with multiple target applications, keeping the conversations separate by assigning each one a channel number (Excel and Word) or identifying each one with a different object in the application like a text box (VB3). MSProject only allows one conversation at a time.

MSProject, Excel, and Word have common keywords, but the actual syntax and usage varies. VB3 has different keywords for properties and methods that serve a similar purpose, and also has a robust set of event procedures. The table below shows the DDE-related keywords in these applications. See the Online Help or manuals of each application for syntax details and examples:

*VB3 also includes a LinkTimeout property, as well as the following event procedures: LinkClose, LinkError, LinkExecute, LinkNotify, and LinkOpen.

The rest of this section uses the Excel keywords to represent the generic commands.

Note:

If the target application is not running when the source macro uses DDEInitiate, then you get a message asking if you want to start the target. For example, if Excel is not running when an MSProject executes DDEInitiate "Excel","System", then you get the message: Linked data not accessible. Start application 'EXCEL.EXE' ?

A DDE conversation can be initiated with a target application even if it the target application is running invisibly (not in the Windows Task List).

An application can't initiate a DDE conversation with itself.

Time Out

Some applications include a DDEExecute timeout argument or a DDE timeout option setting. For example, MSProject has a timeout argument with its DDEExecute command, and VB3 has the LinkTimeout property. Excel's DDEExecute does not have a timeout argument. Setting a timeout allows you to control how long the source application should wait for the target application to finish. If you know the target is going to be taking a long time to finish the command, and if you want your calling application to continue to the next line after the DDEExecute statement without waiting, then you may want to set the timeout to the smallest legal value and trap the timeout error. Of course the source and target applications can only "share" time in a cooperative multitasking environment if they release control occasionally, for example, while waiting for input/output operations or by using commands like DoEvents.

Example

The following MSProject macro tells Excel to open a "really large" workbook. The macro waits the minimum 1 second for Excel to finish and then continues.

Sub DDEProjectMacro()

DDEInitiate "Excel", "System"

On Error Resume Next

DDEExecute "[Open(""d:\excel5\class\big.xls"")]", 1

On Error Goto 0

DDETerminate

'More code can go here if it doesn't

'depend on Big.xls being open

End Sub

Example

This example shows an MSProject macro, DDEProjMacro1, that runs an Excel macro, ExcelMacro1. The MSProject macro quickly continues after the DDEExecute command, even though Excel is in the background waiting for input to its own message. To test this, enter the MSProject macro in MSProject and enter the Excel macro in Excel. Then run the MSProject macro and OK its message. Then switch to Excel and OK its message.

Sub DDEProjMacro1() 'MSProject macro

DDEInitiate "Excel", "system"

On Error Resume Next

DDEExecute "[Run(""ExcelMacro1"")]", 1

On Error Goto 0

DDETerminate

MsgBox "DDEProjMacro1 is done."

End Sub

Sub ExcelMacro1() 'Excel macro run by the above MSProject macro

MsgBox "ExcelMacro1 Message"

End Sub

There is no timeout option when using OLE Automation. There is not a consistent way that all applications handle OLE timeouts. If GetObject or CreateObject fail because the target application is not running, then you usually get an OLE Automation error quickly. If the target application is running, but busy, then the amount of time that the source application waits before giving a message, and the text of that message, depend on the source application. In some cases, GetObject or CreateObject can succeed in establishing an OLE reference, but when you try to use the reference to access the methods/properties of the target application, it might not respond at all, or respond by saying it's busy.

The next two tables illustrate how MSProject and Excel macros deal with OLE timeout problems. The delay times given were derived from tests on a specific machine.

The following table shows some examples of MSProject macros that use OLE Automation to control Excel, and some of the timeout problems that can occur. The bolded macro lines (in the second column) are executing when the messages occur.

The following table shows some examples of Excel macros that use OLE Automation to control MSProject, and some of the timeout problems that can occur. The bolded macro lines (in the second column) are executing when the messages occur.

Using DDE to Control MSProject From Another Application

The file DDEINFO.WRI that shipped with Project 3.0 is not included with Project 4.0, however the information in that file applies to using DDE to control Project 4.0 as well, except where features have been changed or added.

Macros in other applications might use DDE or OLE Automation or both to control MSProject. Both OLE Automation and DDE can do some things, like getting/setting task, resource, and assignment information, but it is usually easier to use OLE Automation. Some things can be directly done only by DDE, like passing arguments to an MSProject sub procedure or assigning a value to an MSProject global variable. Some things can be directly done only by OLE Automation, like getting the list of base calendars, tables, views, and filters.

Other applications can use DDEExecute to make MSProject execute many of its own macro statements. For example, if gVar is declared as a global variable in an MSProject module, then the following Excel macro assigns the value "wow" to gVar:

Sub aaa()

Dim chn As Integer

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

DDEExecute chn, "gVar = ""wow"""

DDETerminate chn

End Sub

The DDEExecute line above causes MSProject to execute the statement

gVar = "wow"

which assigns the value "wow" to the global variable gVar. After the above Excel macro runs, you could go to the Debug Window in MSProject and enter the following to check the value of gVar: ? gVar

Using Poke and Request.

Unique Ids

One drawback to using DDERequest and DDEPoke is that you must specify tasks, resources, or assignments by Unique Ids, not ids or names.

Data Size Limitations

One of the advantages DDERequest and DDEPoke do have when talking to MSProject is the use of the asterisk wildcard character - but this can also be a nightmare depending on how much data is returned and how much data the calling application can deal with. MSProject itself doesn't want to return more than 64K at a time. A VB3 Text Box can only receive 32K at a time (that's the formal spec).

Syntax Variations

MSProject returns requested data (DDERequest) to other applications and receives poked data (DDEPoke) from other applications in a format that uses tabs to delimit fields and newline (chr(13) & chr(10)) characters to separate records. When requesting task or resource records from MSProject, blank rows are skipped. The exact syntax and approach used with DDERequest and DDEPoke depends on the source application. Excel Visual Basic macros convert requested data into a variant "array" that's pretty tricky to work with. VB3 on the other hand retrieves the same requested data as a straight forward string that can be parsed for the tab and newline characters. Of course, you wouldn't normally use DDE at all in an Excel macro or VB3 program to talk to MSProject, because they both support OLE Automation. Applications that don't support OLE Automation are stuck with DDE (if they support it).

The following statements in Excel shows how the names, durations, and start dates of all tasks in P1.mpp can be requested and stored in a variant array variable called DataRequested (assuming P1.mpp is open):

Dim DataRequested As Variant

Dim channel As Integer

channel = DDEInitiate ("Winproj", "P1.mpp")

DataRequested = DDERequest(channel, "T(*,(name,duration,start))")

If only one record is returned (one task in the project) then one index is used and it specifies which of the requested fields you want (by position in the list):

This... Accesses this ...

DataRequested(1) name (of the single task)

DataRequested(2) duration (of the single task)

DataRequested(3) start (of the single task)

But if 2 or more records are returned then two indices are used - the first to specify the task record and the second to specify which of the requested fields you want. For example, to get the fields for the 56th task record:

This... Accesses this ...

DataRequested(56,1) name (of the task in the 56th record)

DataRequested(56,2) duration (of the task in the 56th record)

DataRequested(56,3) start (of the task in the 56th record)

UBound(DataRequested) is the number of (nonblank) records returned, provided at least 2 (nonblank) records are returned, but is the number of requested fields if only one record is returned.

Several of the following Excel macro examples compare how to accomplish a task using DDE versus OLE Automation.

Example

Each of these Excel macros opens the project c:\wprj4dat\P1.mpp.

Sub DDE_12()

Dim SystemChannel

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

DDEExecute SystemChannel, "FileOpen ""c:\wprj4dat\p1.mpp"""

DDETerminate SystemChannel

End Sub

Sub ole_12()

Dim oMSP As Object

Set oMSP = GetObject(, "MSProject.Application")

oMSP.FileOpen "c:\wprj4dat\p1.mpp"

End Sub

Example

Each of these Excel macros displays the name of the active project.

Sub DDE_1()

Dim SystemChannel As Integer

Dim RequestedData As Variant

Dim ActiveProjectName As String

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

RequestedData = DDERequest(SystemChannel, "ActiveProject")

ActiveProjectName = RequestedData(1)

MsgBox ActiveProjectName

End Sub

Sub OLE_1()

Dim oMSP As Object

Set oMSP = GetObject(, "MSProject.Application")

MsgBox oMSP.ActiveProject.Name

End Sub

Example

Each of these Excel macros displays the name of the active project calendar.

Sub DDE_3()

Dim SystemChannel As Integer

Dim ActiveProjectName As String

Dim DocumentChannel As Integer

Dim RequestedData As Variant

Dim CalendarName As String

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

RequestedData = DDERequest(SystemChannel, "ActiveProject")

ActiveProjectName = RequestedData(1)

DocumentChannel = DDEInitiate("winproj", ActiveProjectName)

RequestedData = DDERequest(DocumentChannel, "P(4)")

CalendarName = RequestedData(1)

MsgBox CalendarName

DDETerminate SystemChannel

DDETerminate DocumentChannel

End Sub

Sub ole_3()

Dim oMSP As Object

Set oMSP = GetObject(, "MSProject.Application")

MsgBox oMSP.ActiveProject.Calendar

End Sub

Example

Each of these Excel macros sets the calendar of the active project to "Company Calendar". To test these, make a new base calendar called "Company Calendar", and in Summary Info, choose Standard for the calendar. Then run the Excel macro.

Sub DDE_2()

Dim SystemChannel As Integer

Dim DocumentChannel As Integer

Dim ActiveProjectName As String

Dim RequestedData As Variant

Dim CalendarName As String

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

RequestedData = DDERequest(SystemChannel, "ActiveProject")

ActiveProjectName = RequestedData(1)

DocumentChannel = DDEInitiate("winproj", ActiveProjectName)

'You must put "Company Calendar" in a cell on an Excel sheet

'first; you can't hard code "Company Calendar" into the

'DDEPoke statement

Sheets("Sheet1").Range("A1") = "Company Calendar"

DDEPoke DocumentChannel, "P(4)", Sheets("Sheet1").Range("a1")

DDETerminate SystemChannel

DDETerminate DocumentChannel

End Sub

Sub OLE_2()

Dim oMSP As Object

Set oMSP = GetObject(, "MSProject.Application")

oMSP.ProjectSummaryInfo calendar:="Company Calendar"

End Sub

Example

This MSProject macro displays the list of available task tables. That includes all task tables in Global.mpt and all task tables in the active project. There is no direct way to do this using DDE.

Sub ole7()

Dim oMSP As Object

Dim n As Integer

Set oMSP = GetObject(, "MSProject.Application")

For n = 1 To oMSP.ActiveProject.TaskTableList.Count

MsgBox oMSP.ActiveProject.TaskTableList(n)

Next n

End Sub

RUNNING ANOTHER APPLICATION'S MACROS

MSProject Macros Running Excel Macros

MSProject macros can use DDE and OLE Automation to run Excel macros that take no arguments. It's also possible for an MSProject macro to pass arguments to an Excel procedure and even to get the result back from an Excel function procedure, but only using OLE Automation.

In these examples, it is assumed the Excel macros being called are in the active workbook, and that the macro names are unique.

Example

Each of these MSProject macros runs the Excel macro ExcelSubNoArgs that takes no arguments. The Excel macro ExcelSubNoArgs is shown after the MSProject macros.

Sub DDE_1()

DDEInitiate "Excel", "System"

AppActivate "Microsoft Excel"

DDEExecute "[Run(""ExcelSubNoArgs"")]", 60

DDETerminate

AppActivate "Microsoft Project"

MsgBox "MSProject macro is continuing."

End Sub

Sub OLE_1()

Dim x As Object

Set x = GetObject(, "Excel.Application")

AppActivate "Microsoft Excel"

x.Run "ExcelSubNoArgs"

AppActivate "Microsoft Project"

MsgBox "MSProject macro is continuing."

End Sub

Here's the Excel macro that is called by the above MSProject macros:

Sub ExcelSubNoArgs()

MsgBox "ExcelSubNoArgs message"

End Sub

NOTE: MSProject can't use DDEExecute to pass arguments to an Excel macro. The DDEExecute statement in the MSProject macro below attempts to execute the ExcelSubWithArgs macro above. The MSProject macro below causes the Excel error: Cannot find macro 'ExcelSubWithArgs 480, #1/1/95 10:00:00 AM#'.

Sub DDE_2()

DDEInitiate "Excel", "system"

AppActivate "Microsoft Excel"

DDEExecute _

"[Run(""ExcelSubWithArgs 480, #1/1/95 10:00:00 AM#"")]"

DDETerminate

End Sub

Excel Macros Running MSProject Macros

Excel macros can use DDE and OLE Automation to run MSProject macros that take no arguments. It's also possible for an Excel macro to pass arguments to an MSProject procedure, but only using DDEExecute. It's not possible for an Excel macro to directly read values returned by calling an MSProject function procedure.

In these examples, it is assumed the MSProject macros are either in the Global.mpt or in the active project, and that the macro names are unique.

Example

Each of these Excel macros runs the MSProject macro "ProjectMacro1" shown after the Excel macros. Assume there is only one MSProject macro with that name.

Sub dde9()

Dim SystemChannel As Integer

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

AppActivate "Microsoft Project"

DDEExecute SystemChannel, "ProjectMacro1"

DDETerminate SystemChannel

AppActivate "Microsoft Excel"

End Sub

Sub ole9()

Dim oMSP As Object

Set oMSP = GetObject(, "MSProject.Application")

oMSP.Macro "ProjectMacro1"

End Sub

Here's the MSProject macro that was called by the above Excel macros:

Sub ProjectMacro1()

MsgBox "ProjectMacro1 is running"

End Sub

NOTE: You can't use OLE Automation to pass arguments to MSProject macros. The following Excel macro attempts to use OLE Automation to pass arguments to MSProjectMacro1 from the previous "Try This" example. The Excel macro below causes the MSProject error: Cannot find macro 'ProjectMacro1 "hello", 10'.

Sub ole10()

Dim oMSP As Object

Set oMSP = GetObject(, "MSProject.Application")

'The next line causes the error:

'Cannot find macro 'ProjectMacro1 ""hello"", 10'.

oMSP.Macro "ProjectMacro1 ""hello"", 10"

End Sub

Assigning Values to Another Application's Global Variables

An Excel macro can use DDEExecute to directly assign a value to an MSProject global variable.

An MSProject macro can NOT directly assign a value to an EXCEL global variable, although it can pass an argument to an Excel macro that assigns the passed value to an Excel global variable.

Review of Advantages and Disadvantages of Controlling MSProject by DDE

Here's a review of some of the advantages and disadvantages of using DDE to talk to MSProject from another application:

Advantages

• Faster.
• Easy to get the coordinates of the selected cell.
• Asterisk wild card to access "all" of something.
• Can pass arguments to MSProject macros.
• Can assign values to MSProject global vars.

Disadvantages

• Have to use Unique Ids to access task/resource/assignment data.
• Cryptic syntax.
• Asterisk wild card (overflow problems).
• Can't get list of tables, views, filters, base calendars.
• MSProject must already be running.

Lesson 4 Exercises

1. Write an MSProject macro called AllOneDay (with no arguments) that sets the durations of all tasks in the active project to 1d. Write it so it skips blank rows. Then write an Excel macro that runs the AllOneDay macro. Try writing the Excel macro two ways: once using DDEExecute, and once using OLE Automation. Test each on a project that has some tasks that have a duration different from 1d.



2. Write an MSProject sub procedure called IncreaseCost that takes an integer argument, TaskId, and a currency argument, CostChange. The procedure adds the value of CostChange onto the FixedCost field of the task specified by TaskId. For example, if the current FixedCost of task 2 is $50.00, then the following call will increase it to $80.25:




IncreaseCost 2, 30.25

Write an Excel macro that passes the values 1 and 10.50 to the MSProject sub procedure IncreaseCost. Test it. It should add $10.50 to the FixedCost of task 1 each time you run it.