Solved

Excel query to incorporate parameters

Posted on 2011-03-02
10
447 Views
Last Modified: 2012-05-11
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
Comment
Question by:RTsal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 

Expert Comment

by:30secBoard
ID: 35018592
Are you using VB macros?
This is the best way to managed all the excel features and connect to a DB
0
 

Author Comment

by:RTsal
ID: 35018697
No I am not but I am open to the suggestion
0
 

Expert Comment

by:30secBoard
ID: 35018778
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:mirtheil
ID: 35019241
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
 

Author Comment

by:RTsal
ID: 35020304
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
 
LVL 18

Expert Comment

by:mirtheil
ID: 35020462
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
 

Author Comment

by:RTsal
ID: 35020945
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
 

Expert Comment

by:30secBoard
ID: 35021527
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
 

Accepted Solution

by:
RTsal earned 0 total points
ID: 35026092
I found the solution on this website so I will close the question
http://www.dicks-clicks.com/excel/ExternalData6.htm
0
 

Author Closing Comment

by:RTsal
ID: 35067768
I found the solution myself by searching on the Internet
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question