Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables
Hi All,
You will have to excuse me if this is not the right forum for this sort of question but it seemed like a good place to start. I am trying to return some data from Sql Server, I will try and give as much detail as possible. I have written a very complex query in TSQL which pass's in a selection of defined variables into a selection of select statement, What I would like to do if at all possible is rather that passing the variables into the stored procedure in TSQL and populating tables and then just creating a basic select statement in Microsoft Query, is it at all posssible to get the Excel Sheet to ask for the variables, so in theory and it is very much a theory is there a way of programming such a complex statement into Microsoft Query. I hope that makes some sort of sense and I can paste the code if anyone needs it. Thanks in advance Philip |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables
You can use parameters in a MSQuery query, and you can link them to cells on
your spreadsheet. Whether this will work for you depends on the complexity of your SQL, I suppose, and how you need to use those parameters as variables in your query. You would create the parameters in MSQuery using the notation [], as in Access, then when you are back in Excel a right-click on the query result range will let you set the Parameters option to say where these parameters come from. But, another option that might have even more advantage: Set up a simplified version of the query (with no parameters) in MSQuery, just to get much of the setup work to be done for you without having to code it all. Then, once you have the querytable, you can edit its properties. The CommandText property is the SQL statement, so you should be able to change the CommandText to take care of your entire SQL, no matter how complex, and you can incorporate cell values and VBA variables/calculations however you need to in setting up the query text. This approach would combine the ease of using MSQuery for the initial setup, but give you pretty much complete flexibility on the actual SQL you end up sending to your database. "Phil" wrote: Hi All, You will have to excuse me if this is not the right forum for this sort of question but it seemed like a good place to start. I am trying to return some data from Sql Server, I will try and give as much detail as possible. I have written a very complex query in TSQL which pass's in a selection of defined variables into a selection of select statement, What I would like to do if at all possible is rather that passing the variables into the stored procedure in TSQL and populating tables and then just creating a basic select statement in Microsoft Query, is it at all posssible to get the Excel Sheet to ask for the variables, so in theory and it is very much a theory is there a way of programming such a complex statement into Microsoft Query. I hope that makes some sort of sense and I can paste the code if anyone needs it. Thanks in advance Philip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
Three Variables | Excel Worksheet Functions | |||
Looking up two variables | Excel Programming | |||
Variables | Excel Programming | |||
variables | Excel Programming |