HOWTO: Initiate DDE from Visual Basic for Windows to Excel for Windows

ID: Q75089

The information in this article applies to:

Microsoft Visual Basic Standard and Professional Editions for Windows, versions 2.0, 3.0
Microsoft Visual Basic programming system for Windows, version 1.0
Microsoft Excel for Windows, version 5.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

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

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

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.

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

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

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


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

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

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

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

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

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.

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

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

Exit from Visual Basic.

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

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

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

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.

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.

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.

NOTE: If you have the Ignore Remote Requests option selected in the Excel Workspace dialog box, you will not be able to establish DDE from Visual Basic. Make sure the Ignore Remote Requests option isn't selected.

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


   CONST TRUE = -1
   CONST FALSE = NOT TRUE

REFERENCES

For more information on DDE between Visual Basic and other Windows-based applications, query on the following words in the Microsoft Knowledge Base:


   DDE and Visual Basic

Additional query words:


Keywords          : kbExcel kbVBp300 kbVBp400 kbvbp200 
Version           : WINDOWS:1.0,2.0,3.0,5.0
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: July 13, 1999