Executing a Stored Procedure from Excel Using DDE and Q+E

SUMMARY

The QE.XLM macro that comes with Q+E allows you to execute simple select statements and extract data from the SQL Server into Excel worksheets. However, it does not include options for executing SQL Server-stored procedures. You can implement this type of functionality by creating a custom Excel macro. A macro of this type initiates a DDE (Dynamic Data Exchange) channel with Q+E, sends a command, and processes any results that might be returned.

MORE INFORMATION

You must be using Q+E version 2.5 or later to access a SQL Server.

An example of an Excel macro that executes the sp_helpdb stored procedure is listed below:

 chan=INITIATE("QE","SYSTEM")
 =EXECUTE(chan,"[OPEN('master..sp_helpdb')]")
 NR=REQUEST(chan,"NUMROWS")
 NC=REQUEST(chan,"NUMCOLS")
 =EXECUTE(chan,"[FETCH('EXCEL','SHEET1','R1C1:R"&NR&"C"&NC&"','ALL')]")
 =EXECUTE(chan,"[CLOSE()]")
 =TERMINATE(chan)
 =RETURN()

When executing stored procedures from Excel, there are two limitations worth noting. First, Excel has a formula limit of 255 characters; thus limiting the size of your execution string. This means that your execute statement, stored procedure name, and any parameters you may want to supply must not exceed this limit. Second, if the stored procedure is designed to return results from more than one select statement, only the first set of results will actually be returned.

However, with careful planning of your stored procedures, this situation can be avoided. An example of this would be to insert data from several select statements into a temporary table, and in turn, select the temporary table to retrieve the combined results.

The method for initiating a channel with Q+E and sending commands is described in the "Using Dynamic Data Exchange (DDE)" section of the "Q+E User's Guide." In addition, an in-depth discussion of Excel macros can be found in the "Microsoft Excel Functions and Macros Guide."

REFERENCES

"Q+E For Microsoft Excel."

"Microsoft Excel Function Reference."