PRB: Excel Function Macros Cannot Access SQL Server Data 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
