ID Number: Q75089
1.00
WINDOWS
Summary:
This article describes how to initiate a dynamic data exchange (DDE)
conversation between a Visual Basic client application and a Microsoft
Excel server application.
This article demonstrates how to:
- Prepare a Microsoft Excel for Windows document for active DDE.
- Initiate a cold DDE link (information updated upon request from the
client) between Visual Basic (the client) and Excel (the server).
- Use the LinkRequest method to update information in Visual Basic
(the client) based on information contained in Excel (the server).
- Initiate a hot DDE link (information updated automatically from
server to client) between Visual Basic (the client) and Excel
(the server).
- Use the LinkPoke method to send information from Visual Basic (the
client) to Excel (the server).
- Change the LinkMode property between hot and cold.
This information applies to Microsoft Visual Basic programming system
version 1.0 for Windows and to Microsoft Excel for Windows.
More Information:
A client application sends commands through DDE to the server
application to establish a link. Through DDE, the server provides data
to the client at the request of the client or accepts information at
the request of the client.
The procedure below is as an example of how to establish a DDE
conversation between Visual Basic and Excel for Windows.
First, create the server 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 SERVER.XLS
4. Exit Excel. For this example to function properly, Excel must not
be loaded and running.
Next, create the client application in Visual Basic:
The client is the application that performs the link operations. It
prompts the server to send information or informs the server 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 CtlName
------------ ------- -------
Text1 (not applicable) Text1
Option1 Cold Link ColdLink
Option2 Hot Link HotLink
Command1 Poke Poke
Command2 Request Request
3. Add the following code to the general Declaration section of Form1:
Const TRUE = -1
Const FALSE = 0
Const HOT = 1
Const COLD = 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 SERVER.XLS, the
'spreadsheet that was created earlier.
z% = Shell("C:\EXCEL\EXCEL c:\excel\SERVER.XLS", 1)
z% = DoEvents() 'Process Windows events. This ensures
'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|c:\excel\server.xls" 'Sets up link
'with Excel.
Text1.LinkItem = "R1C1" 'Set link to first cell on spreadsheet.
Text1.LinkMode = COLD 'Establish a cold DDE link.
ColdLink.Value = TRUE
End Sub
5. Add the following code to the Click event procedure of the
Cold Link button:
Sub ColdLink_Click ()
Request.Visible = TRUE 'Make request button valid.
Text1.LinkMode = NONE 'Clear DDE Link.
Text1.LinkMode = COLD 'Reestablish new LinkMode.
End Sub
6. Add the following code to the Click event procedure of the
Hot Link button:
Sub HotLink_Click ()
Request.Visible = FALSE 'No need for button with hot link.
Text1.LinkMode = NONE 'Clear DDE Link.
Text1.LinkMode = HOT 'Reestablish new LinkMode.
End Sub
7. Add the following code to the Click event procedure of the
Request button:
Sub Request_Click ()
'With a cold DDE link this button will be visible and when selected
'it will request an update of information from the server
'application to the client 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 client application to the
'server application.
Text1.LinkPoke
End Sub
You can now run the Visual Basic client 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 CLIENT.
2. From the File menu, choose Make EXE File, and name it CLIENT.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 client application will be loaded and Excel will
automatically be started with the document SERVER.XLS loaded.
6. Make sure the main title bar in Excel reads "Microsoft Excel," NOT
"Microsoft Excel - SERVER.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 Hot 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.
You can also establish DDE between applications at design time. This
is described on page 356 of the "Microsoft Visual Basic: Programmer's
Guide" version 1.0 manual.
For more information on DDE between Microsoft Visual Basic version 1.0
and other Windows applications, query on the following words:
DDE and Visual Basic
Additional reference words: 1.00