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