Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
running proc in excel | Excel Discussion (Misc queries) | |||
Excel 2007 Refresh multiple data connections & pass parameter to S | Excel Discussion (Misc queries) | |||
Parameter queries in Excel 2007 | Excel Discussion (Misc queries) | |||
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Excel Discussion (Misc queries) | |||
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Excel Worksheet Functions |