DDE from Visual Basic for Windows to Excel for Windows

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, versions 2.0 and 3.0

SUMMARY

This article describes how to initiate a dynamic data exchange (DDE) conversation between a Visual Basic destination application and a Microsoft Excel source application.

This article demonstrates how to:

• Prepare a Microsoft Excel for Windows document for active DDE.
• Initiate a manual DDE link (information updated upon request from the destination) between Visual Basic (the destination) and Excel (the source).
• Use the LinkRequest method to update information in Visual Basic (the destination) based on information contained in Excel (the source).
• Initiate a automatic DDE link (information updated automatically from source to destination) between Visual Basic (the destination) and Excel (the source).
• Use the LinkPoke method to send information from Visual Basic (the destination) to Excel (the source).
• Change the LinkMode property between automatic and manual.

MORE INFORMATION

A destination application sends commands through DDE to the source application to establish a link. Through DDE, the source provides data to the destination at the request of the destination or accepts information at the request of the destination.

The procedure below is as an example showing how to establish a DDE conversation between Visual Basic and Excel for Windows.

STEP ONE: Create the Source Spreadsheet in Excel

1. Start Excel. A document (spreadsheet) with Sheet1 as the title is created by default.

2. From the File menu, choose Save As, and save the document (spreadsheet) naming it SOURCE.XLS

3. Exit Excel. For this example to function properly, Excel must not be loaded and running.

STEP TWO: Create the Destination Application in Visual Basic

The destination is the application that performs the link operations. It prompts the source to send information or informs the source that information is being sent to it.

1. Start Visual Basic (VB.EXE). Form1 is created by default.

2. Add the following controls to Form1, and give them the properties indicated:

      Default Name   Caption            Name
      -------------------------------------------------
      Text1          (not applicable)   Text1
      Option1        Manual Link        ManualLink
      Option2        Automatic Link     AutomaticLink
      Command1       Poke               Poke
      Command2       Request            Request
   
   

3. Add the following code to the general Declaration section of Form1:

   Const AUTOMATIC = 1 Const MANUAL = 2 Const NONE = 0

4. Add the following code to the Load event procedure of Form1:

      Sub Form_Load ()
         'This procedure starts Excel and loads SOURCE.XLS, the
         'spreadsheet created above.
         Dim ErrorTries As Integer
         ErrorTries = 0
         On Error GoTo errorhandler
   
         z% = Shell("c:\EXCEL\excel SOURCE.XLS", 1)
   
         DoEvents         'Process Windows events to ensure that
                          'Excel executes before making any attempt
                          'to perform DDE.
   
         Text1.LinkMode = NONE    'Clear DDE link if it already exists.
   
         'Set up link with Excel:
         'Unremark the next line for use with Excel 4.
         'Text1.LinkTopic = "Excel|source.xls"
   
         'Remark out the next line when using Excel 4
         Text1.LinkTopic = "Excel|C:\VB3\[SOURCE.XLS]Sheet1"
   
         Text1.LinkItem = "R1C1"  'Set link to first cell on spreadsheet.
         Text1.LinkMode = MANUAL  'Establish a manual DDE link.
         ManualLink.Value = True
         Exit Sub
   
      errorhandler:
         If Err = 282 And ErrorTries < 15 Then
            ErrorTries = ErrorTries + 1
            DoEvents
            Resume
         Else
            Error Err
         End If
   
      End Sub
   
   

5. Add the following code to the Click event procedure of the Manual Link button:

      Sub ManualLink_Click ()
         Request.Visible = TRUE    'Make request button valid.
         Text1.LinkMode = NONE     'Clear DDE Link.
         Text1.LinkMode = MANUAL   'Reestablish new LinkMode.
      End Sub
   
   

6. Add the following code to the Click event procedure of the Automatic Link button:

      Sub AutomaticLink_Click ()
         Request.Visible = FALSE     'No need for button with automatic link.
         Text1.LinkMode = NONE       'Clear DDE Link.
         Text1.LinkMode = AUTOMATIC  'Reestablish new LinkMode.
      End Sub
   
   

7. Add the following code to the Click event procedure of the Request button:

      Sub Request_Click ()
         'With a manual DDE link this button will be visible and when
         'selected it will request an update of information from the source
         'application to the destination application.
          Text1.LinkRequest
      End Sub
   
   

8. Add the following code to the Click event procedure of the Poke button:

      Sub Poke_Click ()
         'With any DDE link this button will be visible and when selected
         'it will poke information from the destination application to the
         'source application.
         Text1.LinkPoke
      End Sub
   
   

STEP THREE: Run the Visual Basic Destination Application

You have two choices:

• Run the Visual Basic destination application from the Visual Basic environment by skipping to step 4 below.
• Save the application. Then create an .EXE file, and run it from Windows by beginning with step 1 below.

1. From the Visual Basic File menu, choose Save, and save the Form and Project naming both DEST.

2. From the File menu, choose Make EXE File. Name it DEST.EXE.

3. Exit from Visual Basic.

4. Run the application from Windows if an .EXE file or from the Visual Basic environment.

5. Form1 of the destination application will be loaded and Excel will automatically start with the document SOURCE.XLS loaded.

6. Make sure the main title bar in Excel reads "Microsoft Excel," not "Microsoft Excel - SOURCE.XLS." If the title bar is incorrect, make sure the SOURCE.XLS window is not maximized. To do this choose Arrange from the Window menu.

STEP FOUR: Experiment with DDE between Visual Basic and Excel

1. Try typing some text in R1C1 in the spreadsheet. Then click the Request button. The text appears in the text box.

   Be sure to press the ENTER key after entering text into an Excel cell before clicking the Request button in the Visual Basic program. If you don't, a "Timeout while waiting for DDE response" error message will display because of the TEXT1.LINKREQUEST statement. This occurs because while entering text into a cell, Excel is in a polling loop for data entry. No real data is transferred to the cell until you press ENTER. Therefore, Visual Basic continues to request the data from the cell, but Excel does not pay attention to the request until it exits the polling loop, which results in the DDE time-out message.

2. Choose the Automatic Link button and then type some more text in R1C1 of the spreadsheet. The text is automatically updated in the Visual Basic text box.

3. Type some text in the text box in the Visual Basic application and choose the Poke button. The text is sent to R1C1 in the Excel spreadsheet.

For Visual Basic version 1.0 add the following constants to the general declarations of the form:

   CONST TRUE = -1
   CONST FALSE = NOT TRUE

   DDE and Visual Basic