0

This is a problem that has stumped me for a couple of days, and one I've been trying to work on. Unfortunately I very new to working on this stuff, and don't have a lot of resources trying to solve this problem, so if I am unclear or you have a question please post a comment about it.

The problem I am trying to tackle is this:

After getting external data from a SQL Server and having a table in a spreadsheet, say the is called Products, and has columns for productid (column A), productname (column B), supplierid (column C), and categoryname (column D). Is there a way to define a cell, say E1 where you use can use VBA to create a dynamic query. In this dynamic query you would enter some value of categoryname where it would query the table, and give you the results?

Improve this question
4
  • What kind of query are you thinking? Perhaps 2-3 examples would be really useful Commented Jul 1, 2013 at 14:03
  • yes, it's possible. can you be more specific perhaps write in steps what you want to do after you retrieve the table? what is the dynamic query supposed look like? is is supposed to retrieve another set of data from db and paste it in column E? Commented Jul 1, 2013 at 14:03
  • @mehow So when you have your existing table in the spreadsheet, and have not typed anything into that specific cell, the spreadsheet would show all the data from the table (productid, productname, supplierid, categoryname). When you enter a value into that cell it is supposed to query the table you have, and change the table to the results of the query. Say there are three different product categories for example. There is electronic, office, and storage. When you type in storage to that cell, the new table will show every product that is in the storage category and all the columns A B C D. Commented Jul 1, 2013 at 14:18
  • Sounds just like auto filter. Have you tried that? Commented Jul 1, 2013 at 15:06

2 Answers 2

Reset to default
2

It is possible to perform a dynamic query without using any VBA.

Base your table on "From Microsoft Query" (under the "Get External Data" group on the Data tab). Build your query using the Query Wizard, selecting the table columns of interest. On the "Query Wizard - Filter Data" tab, where you would normally say a particular column must have a specific value, instead of the specific value, use just a question mark (for any and all columns that need to be used to filter). When you return your data to Excel, you will be prompted to "Enter Parameter Value" for each ? you used. Just click OK at this stage.

When the Excel "Import Data" dialog appears, click "Properties...". On the Definition tab click the button labelled "Parameters...". For each parameter, choose to "Get the value from the following cell" and to "Refresh automatically when cell value changes". Click the red arrow button (to select a cell) and click in the cell where each parameter will get its value from.

It's a good idea to have a label alongside each cell, and maybe special formatting for the data entry cell. The results table can be on the same worksheet as the parameter entry cells, when you enter a value in the entry cell, a new query will be run and the result will be displayed.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1

Here is a link that might help.

It goes into using VBA to filter.

I found it a while ago when I looked for something similar, hope it helps.

Improve this answer

1 Comment

Add the solution here in the comment apart from giving the link.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.