W
WATjr
We've created an Excel addin (.xla) that has some user defined
functions. One of the functions returns a single result from a store
procedure on SQL Server. The user wants to be able to right click on
the cell that contains the function, and return a result set from a
query using the functions same argument list. In other words, the
function returns a total, and he may want to see the underlying detail
records. I got this to work fairly easily. On the Worksheets
Before_Right_Click event, I check to see the active cell contains the
required udf. If it does, then I parse the cell contents (my udf
formula) to retrieve the arguments that I then pass to a query.
However, a difficulty arises when, technically, there can be another
Excel Formula embedded within my function call.
ie: =MyFunc( 100, 100, #1/3/2003#, #1/30/2003# ) - This will work
fine
as I parse the argument list.
if instead they do this :
=MyFunc( 100, 200, Now()-60, Now()-30 ) - This is a pain in that
I have to evaluate the 3rd and 4th arguments before I pass them on to
the query. Excel already does this in their function calls. Does
anyone have any ideas how I might easily accomplish this. The 3rd and
4th arguments can technically be any valid date function call.
Thanks in advance,
-WATjr.
functions. One of the functions returns a single result from a store
procedure on SQL Server. The user wants to be able to right click on
the cell that contains the function, and return a result set from a
query using the functions same argument list. In other words, the
function returns a total, and he may want to see the underlying detail
records. I got this to work fairly easily. On the Worksheets
Before_Right_Click event, I check to see the active cell contains the
required udf. If it does, then I parse the cell contents (my udf
formula) to retrieve the arguments that I then pass to a query.
However, a difficulty arises when, technically, there can be another
Excel Formula embedded within my function call.
ie: =MyFunc( 100, 100, #1/3/2003#, #1/30/2003# ) - This will work
fine
as I parse the argument list.
if instead they do this :
=MyFunc( 100, 200, Now()-60, Now()-30 ) - This is a pain in that
I have to evaluate the 3rd and 4th arguments before I pass them on to
the query. Excel already does this in their function calls. Does
anyone have any ideas how I might easily accomplish this. The 3rd and
4th arguments can technically be any valid date function call.
Thanks in advance,
-WATjr.