Solved

DDE syntax

Posted on 1997-09-05
4
2,306 Views
Last Modified: 2013-12-29
I would like to know the syntax of the Explorer->view->options->...use dde.
If I look at excel, I see:

DDE Message:   [open("%1")]
Application:        Excel
DDE app not runing: <blank>
Topic:                system

Where is the DDE message documented and where can I learn about DDE.

Martin
0
Comment
Question by:marting
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
dew_associates earned 50 total points
ID: 1695972
Marting: There are several articles on the MS Knowledge Base as well as some not published there but available.
Try the following at the MS Knowledge Base:
Q115975, Q122733, Q100208, Q99939, Q128811, Q125703, Q114277, Q86926

Other info not on the knowledge base follows:
================
This lesson examines the use of  DDE vs. OLE Automation in Project.
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:
 
MSProject      EXCEL      WORD      *VB3

DDEInitiate      DDEInitiate      DDEInitiate      LinkTopic, LinkMode props
DDEExecute      DDEExecute      DDEExecute      LinkExecute method
DDETerminate      DDETerminate      DDETerminate      LinkMode prop
      DDERequest      DDERequest      LinkItem prop, LinkRequest method
      DDEPoke      DDEPoke      LinkItem prop, LinkPoke method
            DDETerminateAll      
*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.
 
Try This
This example has an MSProject macro that uses DDEExecute to send a string to a VB3 program, which displays the string in a text box. The VB3 program is compiled as an executable.
 1.      Create a new directory in File Manager called C:\VB3TEST
 2.      In a new VB3 project, set the LinkMode property of Form1 to 1-Source.
 3.      Create a text box and a command button on Form1. Set the Caption property of the command button to Close, and put the End command in its click procedure.
 4.      Double click any empty part of Form1, and from the Proc dropdown list, choose LinkExecute. Add the code below to the Form_LinkExecute procedure.
      Sub Form_LinkExecute (cmdstr As String, cancel As Integer)
      cancel = False
      Text1.Text = cmdstr
End Sub
 5.      From the File menu, choose "Make Exe File ..." and save it as C:\VB3TEST\VB3PROG.EXE
 6.      From the File menu choose "Save Project" and save everything to the C:\VB3TEST directory accepting the default file names.
 7.      Exit VB3.
 8.      Enter and run the following MSProject macro.
      Sub Sub1()
      Shell "C:\VB3TEST\VB3PROG.EXE", 1
      DDEInitiate "vb3Prog", "Form1"
      s = InputBox("enter some text")
      DDEExecute s
      DDETerminate
End Sub
 
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.

 
State of Excel before the MSProject macro runs.      The MSProject macro.      Result in MSProject after the MSProject macro is run.

Excel is not running.      Sub ProjMacro1()    Dim x As Object    Set x = GetObject(,"Excel.Application")End Sub      OLE Automation error.
The Excel title bar says "Microsoft Project - Book1".A dialog, like File Page Setup, or a message box is open in Excel.      Sub ProjMacro2()   Dim x As Object   Set x = GetObject(,"Excel.Application")   x.Workbooks.AddEnd Sub      The following warning dialog comes up after about five seconds, with options to "Switch To", "Retry", or "Cancel"This action cannot be completed because the EXCEL application (Microsoft Excel - Book1) is busy. Choose "Switch To" to activate EXCEL and correct the problem.
The Excel title bar says "Microsoft Project - Book1".Excel is "Ready". The following Excel macro will be called from MSProject: Sub ExcelMacro1()    MsgBox "wow"End Sub      Sub ProjMacro3()   Dim x As Object   Set x = GetObject(, "Excel.Application")   x.Run "ExcelMacro1"   MsgBox "ProjMacro3 message"End Sub      The following warning dialog comes up after a minimum of about five seconds if you provide any input to MSProject, like clicking the mouse or pressing a key. Your options are to  "Switch To" or "Retry". You can't choose Cancel.This action cannot be completed because the EXCEL application (Microsoft Excel - Book1) is not responding. Choose "Switch To" to activate EXCEL and correct the problem.
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.
 
State of MSProject before the Excel macro runs.      The Excel macro.      Result in Excel after the Excel macro is run.

MSProject is not running.      Sub ExcelMacro1()   Dim x As Object   Set x =  GetObject(,"MSProject.Application")End Sub      OLE Automation error.
A dialog, like File Page Setup, or a message box is open in MSProject.      Sub ExcelMacro2()   Dim x As Object   Set x = GetObject(,"MSProject.Application")End Sub      The following message comes up after about 45 seconds, with OK, Cancel, and Help buttons:The object is not responding.  Continue waiting?If you choose Cancel, you get an OLE Automation error. If you choose OK then you get the following message with OK and Help buttons.Microsoft Excel is waiting for another application to complete an OLE action.It takes you back to the first message when you choose OK.
MSProject is "Ready". The following MSProject macro will be called from Excel: Sub ProjMacro1()    MsgBox "wow"End Sub      Sub ExcelMacro3()   Dim x As Object   Set x = GetObject(, "MSProject.Application")   x.Macro "ProjMacro1"   MsgBox "ExcelMacro1 message"End Sub      The following message comes up after about 60 seconds, with OK and Help buttons:Microsoft Excel is waiting for another application to complete an OLE action.The message repeats each time you choose OK.
Try This
The MSProject macro below attempts to add a new workbook in Excel, but fails if an Excel dialog or message is open.
 1.      Run Excel and from the File menu choose Page Setup.
 2.      Leave the Excel Page Setup dialog open, and switch to MSProject.
 3.      Enter and run the following MSProject macro.
      Sub ProjMacro2()
      Dim x As Object
      Set x = GetObject(,"Excel.Application")
      x.Workbooks.Add
End Sub
 4.      After about 5 seconds you should get the "busy" warning.
 
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
 
Try This
This illustrates an MSProject macro using OLE Automation to pass arguments to an Excel sub procedure.
 1.      Enter the following Excel procedure. This will be called from the MSProject macro in step 2.
      Sub ExcelSubWithArgs(dur As Long, start As Date)
      MsgBox dur & ", " & start
End Sub
 2.      Enter the following MSProject macro and run it. It passes a duration in minutes and a start date to the above Excel macro.
      Sub MSProjectMacro1()
      Dim x As Object
      Set x = GetObject(, "Excel.Application")
      AppActivate "Microsoft Excel"
      x.Run "ExcelSubWithArgs", 480, #1/1/95 10:00:00 AM#
      AppActivate "Microsoft Project"
      MsgBox "MSProject macro is continuing."
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
 
Try This
This illustrates an MSProject macro that uses OLE Automation to pass arguments to an Excel function procedure and assigns the returned value to an MSProject variable.
 1.      Enter the following Excel function procedure. This will be called from the MSProject macro in step 2. The Excel function procedure returns the largest of the three numbers passed to it.
      Function ExcelFnWithArgs(a As Double, b As Double, c As Double)
      Dim maxnum As Double
      maxnum = a
      If b > maxnum Then
         maxnum = b
      End If
      If c > maxnum Then
            maxnum = c
      End If
      ExcelFnWithArgs = maxnum
End Function
 2.      Enter the following MSProject macro and run it. It should display a 10 which is the largest of the three numbers passed to the Excel function above.
      Sub MSProjectMacro1()
      Dim x As Object, result As Double
      Set x = GetObject(, "Excel.Application")
      result = x.Run("ExcelFnWithArgs", 10, 5, 3)
      MsgBox "result is: " & result
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
 
Try This
The Excel macro below uses DDE to pass arguments to an MSProject sub procedure.
 1.      Enter the following MSProject procedure. This will be called from the Excel macro in step 2.
      Sub MSProjectMacro1(s As String, n As Integer)
      AppActivate "Microsoft Project"
      MsgBox "MSProjectMacro1 is running." & Chr(10) & Chr(10) _
                  & "String value passed: " & s & Chr(10) _                                        & "Integer value passed: " & n
      AppActivate "Microsoft Excel"
End Sub
 2.      Enter the following Excel macro and run it. It passes a string and an integer to the MSProject sub procedure above.
      Sub ExcelDDE1()
      Dim SystemChannel As Integer, command As String
      SystemChannel = DDEInitiate("winproj", "system")
      command = "MSProjectMacro1  ""hello"", 10"
      MsgBox "This is the DDEExecute command that will " _
               & "be sent to MSProject: "  & Chr(10) & Chr(10) _
                  & command
      DDEExecute SystemChannel, command
      DDETerminate SystemChannel
End Sub
 
 
Note   If you want to pass the values of Excel variables s and n, instead of literals "hello" and 10, it  might be clearer to declare QUOTE and COMMA constants and use them to build the DDEExecute command string. For example:
Const QUOTE = """"
Const COMMA = ","
command = " MSProjectMacro1 " & QUOTE & s & QUOTE & COMMA & n
 

 
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.
Try This
The Excel macro below assigns values to some global variables declared in an MSProject module.
 1.      In an MSProject module, declare the global string variable gProjStrVar and the global integer variable gProjIntVar.
 2.      Enter and run the following Excel macro.
      Sub ExcelDDE1()
      Dim SystemChannel As Integer
      SystemChannel = DDEInitiate("winproj", "system")
      DDEExecute SystemChannel, "gProjStrVar = ""wow"""
      DDEExecute SystemChannel, "gProjIntVar = 100"
      DDETerminate SystemChannel
End Sub
 3.      Switch to MSProject and get into the Debug Window (from the View menu in the Module Editor). In the Immediate pane of the Debug Window, examine the values of the global variables gProjStrVar and gProjIntVar. They should be wow and 100 respectively.
 
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.
 


0
 

Author Comment

by:marting
ID: 1695973
First, Thank you for taking the time to answer.

The first question was left unanswered: what is the
syntax in the windows95 dialog box for actions on
file types. Specifically I want to be able to combine
DDE commands in that dialog box. (open a word or pdf
document to a specific page for instance).

0
 

Author Comment

by:marting
ID: 1695974
First, Thank you for taking the time to answer.

The first question was left unanswered: what is the
syntax in the windows95 dialog box for actions on
file types. Specifically I want to be able to combine
DDE commands in that dialog box. (open a word or pdf
document to a specific page for instance).

0
 
LVL 25

Expert Comment

by:dew_associates
ID: 1695975
Martin: Have you looked the the Knowledge Base Articles I gave you? Also, there should be a substantive readme file in the DDE SDK from Microsoft. Here's some that I found to give you an example.

SUMMARY
=======
You can use dynamic data exchange (DDE) to communicate directly with any version of Microsoft Word, including international versions. However, if the syntax of the macro commands in the international version is unknown, or if the DDE syntax used needs to communicate with multiple international versions, you must use the SendKeys command.
 
MORE INFORMATION
================
Although macros from one version of Word are translated automatically when a file moves from one version to another (for example, from English to German), you cannot send macro commands in "language-transparent" form to any version of Word for Windows.
 
If you send the following command to Word for Windows:
 
   FilePrint.NumCopies=2
 
the macro works correctly in the English version, but it does not work correctly in the German version. In the German version, the command must read as follows:
 
   DteiDruck.Exemplare=2
 
WORKAROUND
==========
You can send the SendKeys statements through DDE because they are part of the WordBasic language and are not translated for any language version. This provides access to any specific dialog box in any localized version of Word for Windows based on the keystrokes used to invoke the desired command.
 
To call FilePrint and print two copies of the active document in any language version of Word for Windows, use the following lines:
 
Word 6.0, 7.0
-------------
 
   SendKeys "%-{right}{down 11}{enter}2{enter}"
 
Word 2.0
--------
 
   SendKeys "%-{right}{down 10}{enter}"
   SendKeys "2"
 
Note: Word for Windows versions 1.x and 2.x differ in their menu
structure; therefore, to send the above command to any language version of Word for Windows version 1.x, use the following lines:
 
   SendKeys "%-{right}{down 7}{enter}"
   SendKeys "2"
 
Reference(s):
 
"Using WordBasic," by WexTech Systems and Microsoft, Chapter 5
 
"Word for Windows and OS/2 Technical Reference," Chapter 8
 

0

Featured Post

Want to promote your upcoming event?

Are you going to an event? Are you going to be exhibiting at a tradeshow? Talking at a conference? Using a promotional banner in your email signature ensures that your organization’s most important contacts stay in the know and can potentially spread the word about the event.

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
NTFS file system has been developed by Microsoft that is widely used by Windows NT operating system and its advanced versions. It is the mostly used over FAT file system as it provides superior features like reliability, security, storage, efficienc…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now