Solved

Excel query to incorporate parameters

Posted on 2011-03-02
10
443 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

738 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