DRIVING OTHER APPLICATIONS WITH DDE

As easy as it is to program Automation, not all applications can be driven by using Automation. Most applications that run in Windows and use Automation at least use Dynamic Data Exchange (DDE). What DDE provides for developers is exactly what the name implies—the capability to exchange data dynamically from one application to another. DDE is an ancestor of Automation. In fact, the first version of Automation was built over a layer of DDE. DDE has been around since Windows 1.0 and continues to be a standard that most applications follow to communicate with other applications under Windows.

Like Automation, a DDE session (called a conversation) is made up of a client (also known as the controller), such as Access, and the server, such as Excel. Unlike Automation, with DDE the server application must already be running to begin a session with that application. Access works around this by using the Shell() function to start the server.

When a conversation is started, a channel is created. Somewhat like the limited communications that took place during the early space launches, one side of the conversation controls the channel. The client controls the conversation by using a set of available DDE commands. Figure 13.23 shows a generic DDE session.

Understanding DDE Commands Used in Access

DDE commands allow the client to send and receive data, enable the server to execute commands, and terminate the channel. The following sections cover some of the commands used in Access.

Although using DDE from Access to other applications is being discussed here, you need to understand that you can also use DDE to create a channel from another application to Access. The channel is created by using DDEInitiate, discussed next.

DDEInitiate

The DDEInitiate command creates a channel to be opened, if the server is opening and listening. The syntax for this command is

								lngChannel = DDEInitiate(strAppName,
								strTopic)

where

• lngChannel is the long integer used to communicate with the server application in following DDE commands used with that conversation.

• strAppName is an application name, such as WinWord or Excel. For Access, the strAppName is MSAccess.

• strTopic is the “topic” of the conversation. All applications have a System topic, which is the largest unit to use. It also gives information about the current system, including a list of other topics. Other topics can be a spreadsheet for Excel or a file name for Word. The example shown in the next section uses DDEInitiate. The first one is this:

  lngChannel = DDEInitiate("WinWord", "System")
  

  This code line opens a channel to the System topic. A file is opened with a DDEExecute command (explained shortly) and then is DDETerminate'd (also explained shortly). Next, another DDEInitiate command is used to get a direct channel to the document itself with the following code:

  lngChannel = DDEInitiate("WinWord", strFinalDoc)
  

  Here, strFinalDoc is the name of the current document opened in Word.

DDEExecute

The DDEExecute command has the server application perform one of the methods, commands, or statements available to that server. The syntax for this command is

DDEExecute lngChannel,
								strServerCommand
							

where

• lngChannel is the channel number passed back from the DDEInitiate function.

• strServerCommand is whatever command you want the server to perform, placed in quotation marks.

This section focuses on a few of examples that use Word Basic commands available to Word. Later, the section “Performing the Complicated Word Example with DDE” provides examples that use the DDEExecute command.

The first example in this section runs the Word Basic command StartOfDocument, which moves the cursor to the beginning of the document in Word:

DDEExecute lngChannel, "[StartOfDocument]"

The next example deselects any selected text in the document:

DDEExecute lngChannel, "[SetSelRange 0, 0]"

DDEPoke

The DDEPoke command passes data from the client application to the server application. The syntax for DDEPoke is

DDEPoke lngChannel,
								strItem,
								strData
							

where

• lngChannel is a long integer representing the conversation with the topic—a spreadsheet topic, in this case.

• strItem is the field used in which strData will be placed. DDEPoke isn't used in the example later in the section “Performing the Complicated Word Example with DDE.” Instead, picture placing a value in a cell of an Excel spreadsheet:

  DDEPoke lngChannel, "R2C3", "300"
  									

DDERequest

DDERequest retrieves data from the server from the item sent through the channel. Here's the syntax of the command:

DDERequest lngChannel,
								strItem
							

where

• lngChannel is a long integer representing the conversation to the topic—again, a spreadsheet topic, in this case.

• strItem is the item to retrieve. By using a spreadsheet example, you retrieve data from the cell that was filled in with the DDEPoke command:

  DDERequest lngChannel, "R2C3"
  									

DDETerminate

The DDETerminate command terminates a specific DDE channel. Its syntax is

DDETerminate lngChannel
							

where lngChannel is a long integer representing the conversation to the topic.

DDETerminateAll

The DDETerminateAll command terminates all conversations now taking place in Access. The syntax is simply

DDETerminateAll

Performing the Complicated Word Example with DDE

Now that you've seen the available DDE commands, you're ready to examine Listing 13.14, which you can find in the Chap13.mdb database. The DDELetterWordDemo form is used. The event procedure cmdCreateLetter_Click is attached to the OnClick event of the cmdCreateLetter command button.

Listing 13.14. Chap13.mdb: Creating a Letter with DDE

Private Sub cmdCreateLetter_Click()

   Dim dbLocal As DATABASE
   Dim snpReplaceCodes As Recordset
   Dim strCurrAppDir As String
   Dim strFinalDoc As String
   Dim strFileOpenCmd  As String
   Dim strEditReplace As String
   Dim varReplaceWith As Variant
   Dim lngChannel As Long
   Dim lngReturnValue As Long

   On Error GoTo Error_cmdCreateLetter_Click

   '-- Get the applications path and establish the final filename
   Set dbLocal = CurrentDb()
   strCurrAppDir = Left$(dbLocal.Name, InStrRev(dbLocal.Name, ""))
   strFinalDoc = strCurrAppDir & "DemoTest.doc"

   '-- If the final file is already there, delete it.
   On Error Resume Next
   Kill strFinalDoc
   On Error GoTo Error_cmdCreateLetter_Click

   '-- Copy the template so it doesn't get written over.
   FileCopy strCurrAppDir & "WordDemo.DOT", strFinalDoc

   Do
     On Error Resume Next
     lngChannel = DDEInitiate("WinWord", "System")
     If Err.Number = 282 Then
        On Error GoTo Error_cmdCreateLetter_Click
        '-- Open a copy of Word
        lngReturnValue = Shell("c:msofficewinwordWinWord.exe", 1)
     ElseIf Err.Number > 0 Then
        MsgBox "An error has occurred trying to start DDE", vbCritical, _
               "Exiting Demo"
        Exit Sub
     End If
   Loop While lngChannel = 0

   '-- Open the file and then terminate the system channel
   strFileOpenCmd = "[FileOpen """ & strFinalDoc & """]"
   DDEExecute lngChannel, strFileOpenCmd

   DDETerminate lngChannel

   '-- Create a channel directly to the document
   lngChannel = DDEInitiate("WinWord", strFinalDoc)

   '-- Open the table of replace codes then cycle through them.
   Set snpReplaceCodes = dbLocal.OpenRecordset("DDEWordReplaceCodes", _
                           dbOpenSnapshot)

   Do While Not snpReplaceCodes.EOF
     '-- Return to the top of the document for each code.
     DDEExecute lngChannel, "[StartOfDocument]"
     '-- Get the actual value to replace with, then use the
     '-- Word replace.
     varReplaceWith = Eval(snpReplaceCodes!ReplaceWithFieldName)
     strEditReplace = "[EditReplace """ & snpReplaceCodes!CodeToReplace _
          & """, """ & IIf(IsNull(varReplaceWith), " ", _
          CStr(varReplaceWith)) & """,.ReplaceOne]"
     DDEExecute lngChannel, strEditReplace
     '-- If the current replace code is the movie title,
     '-- set bold and italics.
     If snpReplaceCodes!CodeToReplace = "{MOVIETITLE} " Then
        DDEExecute lngChannel, "[StartOfDocument]"
        DDEExecute lngChannel, "[EditFind """ & varReplaceWith & """]"
        DDEExecute lngChannel, "[Italic]"
        DDEExecute lngChannel, "[Bold]"
        DDEExecute lngChannel, "[SetSelRange 0, 0]"
     End If
     snpReplaceCodes.MoveNext
   Loop

   DDETerminate lngChannel
   snpReplaceCodes.Close

   Exit Sub

Error_cmdCreateLetter_Click:

   Beep
   MsgBox "The Following Error has occurred:" & vbCrLf & _
            Err.Description, vbCritical, "DDE Error!"
   Exit Sub

End Sub

Figure 13.24 shows the few conversations used in the code shown in Listing 13.14.

Like the Automation example presented earlier in the section “A More Complex Example of Access-to-Word Automation,” this routine

• Copies a Word template
  
  
• Opens a copy of Word
  
  
• Opens the new file
  
  
• Replaces key fields in the document listed in a snapshot recordset called snpReplaceCodes
  
  
• Changes the {MOVIETITLE} field to boldface and italic, after replacing the field with the value
  
  

It performs all these tasks by using DDE instead of Automation. The result of this routine is exactly the same as the Automation Letter Word Demo. It just demonstrates another way to perform the same task.