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.
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;