Solved

Excel query to incorporate parameters

Posted on 2011-03-02
10
433 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 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