This article was previously published under Q75089
This article has been archived. It is offered "as is" and will no longer be updated.
This article describes how to initiate a dynamic data exchange (DDE)conversation between a Visual Basic destination application and aMicrosoft 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.
A destination application sends commands through DDE to the sourceapplication to establish a link. Through DDE, the source provides data tothe destination at the request of the destination or accepts informationat the request of the destination.
The procedure below is as an example showing how to establish a DDEconversation 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. Itprompts the source to send information or informs the source thatinformation 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:
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 theExcel Workspace dialog box, you will not be able to establish DDE fromVisual Basic. Make sure the Ignore Remote Requests option isn't selected.
For Visual Basic version 1.0 add the following constants to the generaldeclarations of the form:
CONST TRUE = -1 CONST FALSE = NOT TRUE
For more information on DDE between Visual Basic and other Windows-basedapplications, query on the following words in the Microsoft Knowledge Base:
Microsoft Visual Basic 2.0 Standard Edition, Microsoft Visual Basic 3.0 Professional Edition, Microsoft Visual Basic 2.0 Professional Edition, Microsoft Visual Basic 3.0 Professional Edition, Microsoft Visual Basic 1.0 Standard Edition, Microsoft Excel 5.0 Standard Edition