[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Excel query to incorporate parameters

I have an excel query on a Pervasive db and I want to use parameters so that I can change the contents of certain cells (let's say D11-D14) and refresh the query and have it use the contents of those cells for the query. I know there is a parameters dialog but the button is greyed out and I can't access it.
0
RTsal
Asked:
RTsal
  • 5
  • 3
  • 2
1 Solution
 
30secBoardCommented:
Are you using VB macros?
This is the best way to managed all the excel features and connect to a DB
0
 
RTsalAuthor Commented:
No I am not but I am open to the suggestion
0
 
30secBoardCommented:
Excel VB macros its the best choice, and very easy if you don't have experience developing.
Just pres Alt + F11 and add a new module. There you can managed, change and use every single thing about cells. From formula, content, to dimensions and colors. And you can then connect to a DB with code.
If you are really really newbie, you can even record a macro. office 2007, go to view, macro, record a macro, OK, do some changes to your sheet and then you can see the code.
 
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
mirtheilCommented:
As long as you can query the PSQL database though MS Query, you should be able to follow the steps below:

Use data from a cell as a parameter value

   1. On your worksheet, type the values that you want to use as criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) in the query.
   2. Click a cell anywhere in the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) that was created with a parameter query.
   3. On the Data tab, in the Connections group, click Properties.

Excel Ribbon image

   4. In the Properties dialog box, click Connection Properties Button image.
   5. In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
   6. In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
   7. Click Get the value from the following cell.
   8. On the worksheet, click the cell that contains the value that you want to use.

If you want to refresh the data whenever you change the value in the cell, select the Refresh automatically when cell value changes check box.

   9. Click OK.
  10. To refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group. Then click Refresh.

From http://office.microsoft.com/en-us/excel-help/customize-a-parameter-query-HP010216113.aspx
0
 
RTsalAuthor Commented:
Hi Mirtheil, I am sorry but I am using Excel 2003 and your instructions are for Excel 2007.
I have a query which uses data in the query and I want to change a couple of these criteria to parameters so I can simply change a cell contents without editing the query statement.
0
 
mirtheilCommented:
You might still be able to get it working with Excel 2003.  One thing I forgot would be in MS QUery, you query would need a question mark to denote a parameter.  For example, in MS QUery if you connect to DEMODATA and issue the query "SELECT * FROM CLASS WHERE ID > ?" and try to run it, a popup will ask for a value.  I don't have Excel 2003 so I'm not sure if there's a way of automating it like in Office 2007 or later.  
0
 
RTsalAuthor Commented:
When I run it with a parameter it takes a very very long time to return the data (too long) but when I add the value directly to the query without using a parameter to reference a cell, it returns the data quite quickly. Do you now why using a parameter would make it so slow?
0
 
30secBoardCommented:
code wise, 2003 and 2007 are the same. (or almost the same)
dont you worry about it.
In case that you need the parameter to be entered manually, you can use a box. In case you want the value the be in one cell, you can read directly from the cell.
0
 
RTsalAuthor Commented:
I found the solution on this website so I will close the question
http://www.dicks-clicks.com/excel/ExternalData6.htm
0
 
RTsalAuthor Commented:
I found the solution myself by searching on the Internet
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now