Solved

Excel query to incorporate parameters

Posted on 2011-03-02
10
435 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
  • 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now