PRB: Excel's =REQUEST() from DDEML Application Returns #N/A

• Microsoft Windows Software Development Kit (SDK) versions 3.1
• Microsoft Win32 Application Programming Interface (API) included with:

      - Microsoft Windows NT versions 3.5 and 3.51
      - Microsoft Windows 95 version 4.0
  

SYMPTOMS

When executing the =REQUEST() macro to request data from a DDEML server application, Excel returns a value of "#N/A", although the server application returned a valid data handle from the request.

CAUSE

When Excel executes a =REQUEST, it requests data in the most efficient format available. Verifying through DDESPY when Excel executes the =REQUEST macro, one can see that Excel sends out a request for data for each format, in this order:

   XLTable  (Excel fast table format)
   BIFF4    (Excel 4.0 file format)
   BIFF3    (Excel 3.0 file format)
   SYLK     (Symbolic Link)
   WK1      (Lotus 1-2-3 release 2 format)
   CSV      (comma-delimited text)
   TEXT     (CF_TEXT)
   RTF      (rich text format)
   DIF      (data interchange format)

In response to a request, a DDEML server application that supports only one format (for example, the CF_TEXT format) may return a data handle in CF_TEXT format, regardless of the format being requested. When Excel sends its first request for data in XLTable format, this server application returns a data handle in CF_TEXT format, as demonstrated in the code below:

   case XTYP_REQUEST:
      if ((ghConv == hConv) &&
           (!DdeCmpStringHandles (hsz1, hszTopicName)) &&
           (!DdeCmpStringHandles (hsz2, hszItemName)))  {

              lstrcpy (szBuffer, "The Simpsons");
              return (DdeCreateDataHandle (idInst,
                                 szBuffer,
                                 lstrlen (szBuffer)+1,
                                 0L,
                                 hszItemName,
                                 CF_TEXT,
                                 0);
      }
      return (HDDEDATA)NULL;

RESOLUTION

In response to a request, a DDEML server application should return a valid data handle only for the format it supports. When processing an XTYP_REQUEST transaction, the server application should first check whether the data being requested is in its supported format; if so, the server application should return an appropriate data handle. Otherwise, the server application should return NULL.

The code above can be modified as follows to check for this condition:

   case XTYP_REQUEST:
      if ((ghConv == hConv) &&
           (!DdeCmpStringHandles (hsz1, hszTopicName)) &&
           (!DdeCmpStringHandles (hsz2, hszItemName)) &&
           (wFmt == CF_TEXT))  {     // Add this to the if clause
                                     // to check if data is being requested
                                       // in one of its supported formats.

                lstrcpy (szBuffer, "Fred Flintstone");
                return (DdeCreateDataHandle (idInst,
                                   szBuffer,
                                   lstrlen (szBuffer)+1,
                                   0L,
                                   hszItemName,
                                   CF_TEXT,
                                   0);
        }
        return (HDDEDATA)NULL;