Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel SQL query
Dear all,
I'm trying to add to my Excel spreadsheet a query that goes to the external database (also an Excel file) and of course pulls out certain data. The problem is that the typical query wizzard enables entering as a parameter only fixed value. My question is if it's possible to add as a criteria valule reference link to selected cell in the spreadsheet. In other word to make the query take the parameter form the certain cell in the spreadsheet. Here is an example of condition: ........WHERE (`Sheet1$`.Year=2005) AND (`Sheet1$`.Month=6).......I'd like the 'month' parameter to be taken from the specified cell ....how to do it ? Is it possible in Excel SQL ? Thanks in advance for your help... cheers |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel SQL query
gogo wrote:
I'm trying to add to my Excel spreadsheet a query that goes to the external database (also an Excel file) and of course pulls out certain data. The problem is that the typical query wizzard enables entering as a parameter only fixed value. My question is if it's possible to add as a criteria valule reference link to selected cell in the spreadsheet. In other word to make the query take the parameter form the certain cell in the spreadsheet. Here is an example of condition: .......WHERE (`Sheet1$`.Year=2005) AND (`Sheet1$`.Month=6).......I'd like the 'month' parameter to be taken from the specified cell ....how to do it ? Is it possible in Excel SQL ? You need to set up the condition as a prompt parameter, then change it to a range parameter. If you're using MSQuery, you would use brackets ..Month = [Enter Month] to create a prompt parameter. Then, back in Excel, you click on the Parameters button of the External Data Toolbar and change the parameter type to Range. If you're doing this in SQL or VBA (by building a SQL string), it's the question mark (?) that holds the place of the parameter ..Month=? Again, you click the Parameters button to change the type. See http://www.dicks-clicks.com/excel/Ex...htm#Parameters for more information and examples. -- Dick Kusleika MVP - Excel Daily Dose of Excel http://www.dicks-blog.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel SQL query
Dick you are the BEST ! Thanks a lot for your help....you saved me a lot of
work and frustration ;-) -- Dick Kusleika MVP - Excel Daily Dose of Excel http://www.dicks-blog.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel SQL query
one more request , what needs to be entered to the cell in order to select
all items of the parameter e.g. all months from the range....tried * but doesn't work Użytkownik "facet" napisał w wiadomości ... Dick you are the BEST ! Thanks a lot for your help....you saved me a lot of work and frustration ;-) -- Dick Kusleika MVP - Excel Daily Dose of Excel http://www.dicks-blog.com |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Excel SQL query
facet wrote:
one more request , what needs to be entered to the cell in order to select all items of the parameter e.g. all months from the range....tried * but doesn't work As far as I know, you can't. Here's a workaround: Put two parameters even though you only need one; a greater than or equal to and a less than or equal to. Like this: SELECT CUSTOMER.Name, CUSTOMER.City FROM CUSTOMER WHERE (CUSTOMER.City=? And CUSTOMER.City<=?) Now in Excel, point the first parameter to J1 and the second parameter to K1. You can automatically refresh on J1 changing, but not K1. In K1, put =IF(LEN(J1)=0,"zzzz",J1) Where zzzz is the highest possible value for whatever datatype you're using. In this example, if you put Omaha in J1, then Omaha will appear in K1 and it will return all customers from that city. If you delete J1, then K1 will be zzzz and it will show all customers. A little kludgy, but that's the best I could do. I've crossposted this to the querydao group in case someone over there knows a better way. I've set the follow up back to programming, so any responses will show up here. -- Dick Kusleika MVP - Excel Daily Dose of Excel http://www.dicks-blog.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel SQL query
Once again thanks a lot for your efford.... this would resolve my issue in
110% ;-) Użytkownik "Dick Kusleika" napisał w wiadomości ... facet wrote: one more request , what needs to be entered to the cell in order to select all items of the parameter e.g. all months from the range....tried * but doesn't work As far as I know, you can't. Here's a workaround: Put two parameters even though you only need one; a greater than or equal to and a less than or equal to. Like this: SELECT CUSTOMER.Name, CUSTOMER.City FROM CUSTOMER WHERE (CUSTOMER.City=? And CUSTOMER.City<=?) Now in Excel, point the first parameter to J1 and the second parameter to K1. You can automatically refresh on J1 changing, but not K1. In K1, put =IF(LEN(J1)=0,"zzzz",J1) Where zzzz is the highest possible value for whatever datatype you're using. In this example, if you put Omaha in J1, then Omaha will appear in K1 and it will return all customers from that city. If you delete J1, then K1 will be zzzz and it will show all customers. A little kludgy, but that's the best I could do. I've crossposted this to the querydao group in case someone over there knows a better way. I've set the follow up back to programming, so any responses will show up here. -- Dick Kusleika MVP - Excel Daily Dose of Excel http://www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Edit Query from Excel will not open query in MSQuery | Excel Programming | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |