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

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.

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

lm;351074 Wrote:
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 realize this is a late reply, but after searching the internet about
the same problem today (and getting nowhere), I stumbled upon a simple
solution. (This works for Excel 2007, but there may be a similar
solution with earlier versions.)

Put your query into Microsoft Query using static values in place of
where you would like your parameters. It will still alert you to the
fact that it can't display graphically, but that's ok; it will let you
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 on
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. It
will ask you for the value of your parameters, and you can indicate the
cells you want it to reference.

Done.


--
shepten
------------------------------------------------------------------------
shepten's Profile: http://www.thecodecage.com/forumz/member.php?userid=732
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98256