filter by cell value when importing external data via ms query

[leanne123]

I have an ODBC connection to an sql database. I have created a DSN file and want to filter the data I bring back by an invoice number. However instead of typing this invoice number into MS query filter feature, I want MS query to find it in a cell on one of the tabs in the file. Is this possible?

 

[SkipVought]

If you are using MS Query (Data > Import External Data...), you can make a Parameter Query using a value in a cell on the sheet containing the QueryTable.

Alternatively, it can be done with simple code. Post in Forum707, if you need to pursue this option.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[leanne123]

How do I make a parameter query using the 'query wizard' filter data? What is the syntax for point to a cell in Excel?

 

[SkipVought]

Data > Import External Data > Parameters.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[leanne123]

my parameters option is greyed out, any idea why? I tried to access it from the original queried data tab and a new empty tab?

 

[SkipVought]

You must first EDIT your query, substituting the Criteria VALUE for something like this...
[Enter Value]

The Query Manager will interpret this as a parameter, and when you File > Return Data to Excel, you will be prompted for a value.

Now the Parameters option will be available. Select the THIRD option in the dialog, specifying the appropriate CELL reference, AND check the REFRESH on change box.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}