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

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)
				

Knowing what formats it can handle best, Excel requests data in the most efficient format first, and so on, until it finds one that the server application supports. At this point, Excel stops sending further requests.

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;
				

Because Excel expected to receive data in the format it had requested (that is, XLTable format), and instead received date in CF_TEXT format, Excel returns #N/A, not knowing how to handle the data it received.

   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;