ID Number: Q69819
1.10 1.11 4.20
OS/2
Summary:
SYMPTOMS
When attempting to create an Excel function macro that will invoke
a dynamic data exchange (DDE) channel with SQL Server and select
data based on several arguments, no results are received while
attempting to call the function. For example, the following
function macro yields no results:
=Function Macro
=RESULT(1)
=ARGUMENT("clr")
=ARGUMENT("sz")
chan=INITIATE("QE","select count(*) from widgets
where color='"&clr&"' and size='&sz&'")
data=REQUEST(chan,"R1:C1")
=TERMINATE(chan)
=RETURN(data)
CAUSE
Excel function macros are designed to make calculations based on
arguments passed to them. They do not have the ability to carry out
actions and/or modifications. Therefore, you cannot initiate a DDE
conversation with Q+E from a function macro.
RESOLUTION
To retrieve data from SQL Server, you must use command macros. For
example, the function macro listed above could be changed to a
command macro in the following manner:
=Command Macro
=DEFINE.NAME ("clr",SALES.XLS!clr)
=DEFINE.NAME ("sz",SALES.XLS!sz)
chan=INITIATE("QE","select count(*) from widgets
where color='"&clr&"' and size='&sz&'")
data=REQUEST(chan,"R1:C1")
=TERMINATE(chan)
=FORMULA (data,SALES.XLS!result_field)
=RETURN()
When executed, this command macro selects the desired data from SQL
Server based on the current values of defined names on a worksheet.
The data is then placed on the spreadsheet in a cell referenced by
the defined name called result_field.
Additional reference words: 1.10 1.11 4.20