DDE From Visual Basic to Excel For Windows

See AlsoVMZ7S4

This article is reprinted from the Microsoft Knowledge Base.  To view the article, maximize your help window.  This information applies to 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 of how to establish a DDE

conversation between Visual Basic and Excel for Windows.

First, create the source spreadsheet in Excel:

1. Start Excel, and a document titled "SHEET1" will be created by

   default.

2. From the File menu, choose Save As, and save the document with the

   name SOURCE.XLS

4. Exit Excel. For this example to function properly, Excel must not

   be loaded and running.

Next, 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.

1. Start Visual Basic (VB.EXE), and Form1 will be created by default.

2. Create the following controls with the following properties on

   Form1:

      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 will start Excel and load SOURCE.XLS, the

    'spreadsheet that was created earlier.

    z% = Shell("EXCEL SOURCE.XLS", 1)

    z% = DoEvents()    'Process Windows events. This insures

     'that Excel will be executed before

     'any attempt is made to perform DDE.

    Text1.LinkMode = NONE    'Clears DDE link if it already exists.

    Text1.LinkTopic = "Excel|source.xls" 'Sets up link

     'with Excel.

    Text1.LinkItem = "R1C1"  'Set link to first cell on spreadsheet.

    Text1.LinkMode = MANUAL  'Establish a manual DDE link.

    ManualLink.Value = TRUE

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

You can now run the Visual Basic destination application from the Visual Basic environment (skip

to step 4 below) or you can save the

application and create an .EXE file and run that from Windows (start

from step 1 below):

1. From the Visual Basic File menu, choose Save, and save the Form and

   Project with the name DEST.

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

3. Exit Visual Basic.

4. Run the application (from Windows if an .EXE file or from the Run

   menu if from the Visual Basic environment).

5. Form1 of the destination application will be loaded and Excel will

   automatically be started 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, then

   from the Window menu choose Arrange All.

You can now experiment with DDE between Visual Basic and Excel:

1. Try typing some text in R1C1 in the spreadsheet and then select 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, or else a "Timeout while waiting for DDE response" error

   message will be displayed from the TEXT1.LINKREQUEST statement.

   This occurs because while entering text into a cell, Excel is in a

   polling loop for data entry, and no real data is transferred to the

   cell until you press ENTER. Therefore, Visual Basic keeps

   attempting 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.

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 is NOT

selected.