Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
Hi,
I'm used to using MS Query to return data to Excel, but there's a whole stack of queries that I run each month (literally, dozens) where the date range needs to be changed. I'm wondering whether I could put the chosen date ranges into an excel spreadsheet, maybe in the format: Col 1 Col 2 Col 3 Col 4 sheet query start date end date ....and use VBA to modify the SQL for me. Alternatively, can the SQL itself be written to request a parameter from a particular location? Any ideas? This could be a massive time saver! Cheers, Tom. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
I usally start by Recording a macro when I perform a querry. Then I modify
te querry to make it general purpose. if you post the recorded macro I can make the modification you require. to start a Record macro from worksheet 1) Tools - Macro - Record New Macro 2) Perform the querry 3) Tools - Macro - Stop Recording "mr tom" wrote: Hi, I'm used to using MS Query to return data to Excel, but there's a whole stack of queries that I run each month (literally, dozens) where the date range needs to be changed. I'm wondering whether I could put the chosen date ranges into an excel spreadsheet, maybe in the format: Col 1 Col 2 Col 3 Col 4 sheet query start date end date ...and use VBA to modify the SQL for me. Alternatively, can the SQL itself be written to request a parameter from a particular location? Any ideas? This could be a massive time saver! Cheers, Tom. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
Thanks Joel,
I really should have done that earlier. I'd (stupidly) assumed that a recording macro wouldn't track my activity within MS Query. I play about with VBA a bit, so now I've got a recorded one to play with, I can probably work it out from here. Thanks so much for pointing me in the right direction. Cheers. Tom. "Joel" wrote: I usally start by Recording a macro when I perform a querry. Then I modify te querry to make it general purpose. if you post the recorded macro I can make the modification you require. to start a Record macro from worksheet 1) Tools - Macro - Record New Macro 2) Perform the querry 3) Tools - Macro - Stop Recording "mr tom" wrote: Hi, I'm used to using MS Query to return data to Excel, but there's a whole stack of queries that I run each month (literally, dozens) where the date range needs to be changed. I'm wondering whether I could put the chosen date ranges into an excel spreadsheet, maybe in the format: Col 1 Col 2 Col 3 Col 4 sheet query start date end date ...and use VBA to modify the SQL for me. Alternatively, can the SQL itself be written to request a parameter from a particular location? Any ideas? This could be a massive time saver! Cheers, Tom. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
Better to move away from MS Query and code with ADO where you have
parameterized queries and a lot of other useful options. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... Hi, I'm used to using MS Query to return data to Excel, but there's a whole stack of queries that I run each month (literally, dozens) where the date range needs to be changed. I'm wondering whether I could put the chosen date ranges into an excel spreadsheet, maybe in the format: Col 1 Col 2 Col 3 Col 4 sheet query start date end date ...and use VBA to modify the SQL for me. Alternatively, can the SQL itself be written to request a parameter from a particular location? Any ideas? This could be a massive time saver! Cheers, Tom. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
It probably would be better, but I'm still getting to grips with VBA and SQL.
;) I'm a project manager (not an IT one) rather than a techie, although I do as much techie stuff as I can as I don't like to be helpless... Cheers. "RB Smissaert" wrote: Better to move away from MS Query and code with ADO where you have parameterized queries and a lot of other useful options. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... Hi, I'm used to using MS Query to return data to Excel, but there's a whole stack of queries that I run each month (literally, dozens) where the date range needs to be changed. I'm wondering whether I could put the chosen date ranges into an excel spreadsheet, maybe in the format: Col 1 Col 2 Col 3 Col 4 sheet query start date end date ...and use VBA to modify the SQL for me. Alternatively, can the SQL itself be written to request a parameter from a particular location? Any ideas? This could be a massive time saver! Cheers, Tom. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
There really isn't that much to learn there only a few objects, methods and
properties. Then there always is the public.data.ado NG to ask if you get stuck. MS Query is fine for a quick query from the Excel interface, but if you get into anything that goes a bit further you are better of with ADO. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... It probably would be better, but I'm still getting to grips with VBA and SQL. ;) I'm a project manager (not an IT one) rather than a techie, although I do as much techie stuff as I can as I don't like to be helpless... Cheers. "RB Smissaert" wrote: Better to move away from MS Query and code with ADO where you have parameterized queries and a lot of other useful options. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... Hi, I'm used to using MS Query to return data to Excel, but there's a whole stack of queries that I run each month (literally, dozens) where the date range needs to be changed. I'm wondering whether I could put the chosen date ranges into an excel spreadsheet, maybe in the format: Col 1 Col 2 Col 3 Col 4 sheet query start date end date ...and use VBA to modify the SQL for me. Alternatively, can the SQL itself be written to request a parameter from a particular location? Any ideas? This could be a massive time saver! Cheers, Tom. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
Thanks. That sounds as if it could be worth thinking about.
I've got someone who does loads in ms query and this could be a good next step for them. "RB Smissaert" wrote: There really isn't that much to learn there only a few objects, methods and properties. Then there always is the public.data.ado NG to ask if you get stuck. MS Query is fine for a quick query from the Excel interface, but if you get into anything that goes a bit further you are better of with ADO. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... It probably would be better, but I'm still getting to grips with VBA and SQL. ;) I'm a project manager (not an IT one) rather than a techie, although I do as much techie stuff as I can as I don't like to be helpless... Cheers. "RB Smissaert" wrote: Better to move away from MS Query and code with ADO where you have parameterized queries and a lot of other useful options. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... Hi, I'm used to using MS Query to return data to Excel, but there's a whole stack of queries that I run each month (literally, dozens) where the date range needs to be changed. I'm wondering whether I could put the chosen date ranges into an excel spreadsheet, maybe in the format: Col 1 Col 2 Col 3 Col 4 sheet query start date end date ...and use VBA to modify the SQL for me. Alternatively, can the SQL itself be written to request a parameter from a particular location? Any ideas? This could be a massive time saver! Cheers, Tom. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
Just a couple of quick questions:
What's the difference between ADO and ADO.NET? - Which do I need for Excel? Where can I learn (idiot level) about this? Is there a good book or website? Cheers. "RB Smissaert" wrote: There really isn't that much to learn there only a few objects, methods and properties. Then there always is the public.data.ado NG to ask if you get stuck. MS Query is fine for a quick query from the Excel interface, but if you get into anything that goes a bit further you are better of with ADO. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... It probably would be better, but I'm still getting to grips with VBA and SQL. ;) I'm a project manager (not an IT one) rather than a techie, although I do as much techie stuff as I can as I don't like to be helpless... Cheers. "RB Smissaert" wrote: Better to move away from MS Query and code with ADO where you have parameterized queries and a lot of other useful options. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... Hi, I'm used to using MS Query to return data to Excel, but there's a whole stack of queries that I run each month (literally, dozens) where the date range needs to be changed. I'm wondering whether I could put the chosen date ranges into an excel spreadsheet, maybe in the format: Col 1 Col 2 Col 3 Col 4 sheet query start date end date ...and use VBA to modify the SQL for me. Alternatively, can the SQL itself be written to request a parameter from a particular location? Any ideas? This could be a massive time saver! Cheers, Tom. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
ADO.NET is for the dotnet languages like C# etc. so you want ADO.
Plenty of good tutorials on the web to get started. Just Google on something like ADO tutorial. RBS On 7 Jan, 14:51, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote: Just a couple of quick questions: What's the difference between ADO and ADO.NET? - Which do I need for Excel? Where can I learn (idiot level) about this? *Is there a good book or website? Cheers. "RB Smissaert" wrote: There really isn't that much to *learn there only a few objects, methods and properties. Then there always is the public.data.ado NG to ask if you get stuck. MS Query is fine for a quick query from the Excel interface, but if you get into anything that goes a bit further you are better of with ADO. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... It probably would be better, but I'm still getting to grips with VBA and SQL. ;) I'm a project manager (not an IT one) rather than a techie, although I do as much techie stuff as I can as I don't like to be helpless... Cheers. "RB Smissaert" wrote: Better to move away from MS Query and code with ADO where you have parameterized queries and a lot of other useful options. RBS "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... Hi, I'm used to using MS Query to return data to Excel, but there's a whole stack of queries that I run each month (literally, dozens) where the date range needs to be changed. I'm wondering whether I could put the chosen date ranges into an excel spreadsheet, maybe in the format: Col 1 * Col 2 * Col 3 * * * *Col 4 sheet *query *start date *end date ...and use VBA to modify the SQL for me. Alternatively, can the SQL itself be written to request a parameter from a particular location? Any ideas? *This could be a massive time saver! Cheers, Tom.- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
MS Query is set up to use parameters easily - you can use square
brackets with a space in between in the interface (not the wizard, the advanced interface) for the criteria values. When you run or refresh the query Excel will display a dialog where you can choose to use a range (cell)/ formula or value for the parameter and whether to always use this range/formula/value for this parameter. I've used this feature with input boxes/userforms extensively with success. If you're editing the SQL statement directly, you can use a question mark for the parameter value and Excel will behave as described. (The ui is just writing SQL based on your input anyhow) You may run into some trouble trying to record complex queries (using the advanced interface) as you write them. (can't record) You can work around this by writing the query then record refreshing it. Cliff Edwards |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Driven Query
Thanks Cliff. That sounds like the best answer yet.
Tom. "ward376" wrote: MS Query is set up to use parameters easily - you can use square brackets with a space in between in the interface (not the wizard, the advanced interface) for the criteria values. When you run or refresh the query Excel will display a dialog where you can choose to use a range (cell)/ formula or value for the parameter and whether to always use this range/formula/value for this parameter. I've used this feature with input boxes/userforms extensively with success. If you're editing the SQL statement directly, you can use a question mark for the parameter value and Excel will behave as described. (The ui is just writing SQL based on your input anyhow) You may run into some trouble trying to record complex queries (using the advanced interface) as you write them. (can't record) You can work around this by writing the query then record refreshing it. Cliff Edwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Web Query for Java Applet driven data website | Excel Discussion (Misc queries) | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
How to choose if I use a parameter or not in a parameter query | Excel Discussion (Misc queries) | |||
parameter and macro driven pivot table filter? | Excel Programming | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |