stevensonk (8/13/2008)
I have created many queries using Excel as my tool. Instead of stored procedures I use Views because they allow parameters. create a view in SQL and in the SQL box in EXcel you would put all the fields that you will be selecting from your View and then you can paramaterize. It works great!
Excuse my ignorance please but how can I use parameters in a View. I have tried but it does not work. I remember in Access queries you could use "=[parameter1]" but that doe not work in Views. I have tried to use the "@parameter1" like we do in stored procs and it does not work. I would like to use views rather than stored procedures for ad hoc queries. You see, I have a client of whom I taught some managers to use views as adhoc queries.
The next thing, David, I get a "subscript out of range" error when I use your code in Office 2003 and 2007. Canyou or anyone else help me on this. I think that using Excel this way is cool because I have to either copy the data from SQL Server after I ran the sp, view or just a plain query and paste it into excel or I have to export the data to Excel.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)