Excel 2007 Data -- SQL Server proc or function with parameter

[lm]

I need to pull data from SQL Server into Excel 2007. The data requires that a
date parameter be passed in -- and I can access the data either with a table
function [select * from myfunc(?)] or a stored procedure [exec myproc ?].

However, after spending an hour or so messing around with the data import
features, it seems that the SQL Server choice does not allow parameters, and
the Microsoft Query version does not allow parameters for queries that it
can't display graphically. Even if I mess around the MS Query version
afterwards, I can't seem to get this to work.

Is this an oversight on my part? How should I be approaching this?

I would like the parameter to be populated by a cell on a worksheet, but
prompting the user to enter it is also acceptable. I'd like to avoid VBA or
Macros for this.

 

[shepten]

However, after spending an hour or so messing around with the dat
import
features, it seems that the SQL Server choice does not allo
parameters, and
the Microsoft Query version does not allow parameters for queries tha
it
can't display graphically. Even if I mess around the MS Query version
afterwards, I can't seem to get this to work.

Is this an oversight on my part? How should I be approaching this?

I realize this is a late reply, but after searching the internet abou
the same problem today (and getting nowhere), I stumbled upon a simpl
solution. (This works for Excel 2007, but there may be a simila
solution with earlier versions.)

Put your query into Microsoft Query using static values in place o
where you would like your parameters. It will still alert you to th
fact that it can't display graphically, but that's ok; it will let yo
save it.

Next, in Excel, go to Data, Connections. Select your connection (i.e
the query you just put in) and click on the Properties button. Click o
the Definition tab.

In the Command Text box is your query. And you can change it.
Remove your static values and put in your parameters. Hit Okay. I
will ask you for the value of your parameters, and you can indicate th
cells you want it to reference.

Done