DDE Macro with Microsoft Excel as Client & FoxPro as Server

• Microsoft FoxPro for Windows, versions 2.5x, 2.6, 2.6a

SUMMARY

FoxPro for Windows can be used as a dynamic data exchange (DDE) server if DDEDATA.APP is run before the DDE link is established from Microsoft Excel. DDEDATA.APP, which is included with FoxPro for Windows, sets up FoxPro as a DDE server.

Connecting to FoxPro as a DDE server is a little different than connecting to other DDE servers from Microsoft Excel. See below for an example of Microsoft Excel Visual Basic for Applications macro code used to make the connection to FoxPro.

MORE INFORMATION

Set Up FoxPro as a DDE Server

To set up FoxPro as a server, perform the following:

1. Using Project Manager, open the DDEDATA.PJX file located in the <FoxPro_directory>\SAMPLE\DDE subdirectory.

2. Build the application, using the default name DDEDATA.APP.

3. Run DDEDATA.APP.

Connect to FoxPro from Microsoft Excel Using Visual Basic for Applications

In Microsoft Excel, type the following lines of code in a Visual Basic for Applications module sheet in a workbook:

   Sub foxddetest()

     ChannelNumber = Application.DDEInitiate _
   ("ddedata","c:\fpw26\tutorial\;table invoices")

     returnList = Application.DDERequest(ChannelNumber, "firstrow")

     For i = LBound(returnList) To UBound(returnList)
       Cells(2, i).Formula = returnList(i)
     Next i

     Application.DDETerminate ChannelNumber

   End Sub

The DDERequest() command's first argument is the channel number (or its variable). The second argument is the requested item. Items that can be requested include: ALL, DATA, FIELDCOUNT, FIELDNAMES, FIRSTROW, LASTROW, NEXTROW, and PREVROW. Other topics can be programmed within FoxPro beforehand. This example requests the information from the first row of the database, which is returned to Microsoft Excel in an array.

The For ... Next loop retrieves the information and places it in the active sheet of the workbook. This macro should be run from a worksheet, not the module sheet, through the Tools and Macros menu options.

REFERENCES

FoxPro for Windows "Developer's Guide," version 2.5, pages D12-12 to D12-20

Microsoft Excel "Visual Basic User's Guide," version 5.0, pages 209-217