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

ID: Q107980

The information in this article applies to:

Microsoft Windows Software Development Kit (SDK) 3.1
Microsoft Win32 Application Programming Interface (API), used with:
- Microsoft Windows NT, versions 3.5, 3.51
- Microsoft Windows 95

SYMPTOMS

When executing the =REQUEST() macro to request data from a DDEML server application, Excel returns a value of #N/A, even though 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)

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) &amp;&amp;
           (!DdeCmpStringHandles (hsz1, hszTopicName)) &amp;&amp;
           (!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.

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) &amp;&amp;
           (!DdeCmpStringHandles (hsz1, hszTopicName)) &amp;&amp;
           (!DdeCmpStringHandles (hsz2, hszItemName)) &amp;&amp;
           (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;

Additional query words: 3.10 3.50 4.00

Keywords :
Version : WINDOWS:3.1,95; winnt:3.5,3.51
Platform : WINDOWS winnt
Issue type : kbprb