Microsoft Query Using a List of Criteria

[Fearra]

Hello, this is my first post though I've pulled on this forum as a resource for learning various tricks in Excel in the past. I've got a conundrum that I've been trying to figure out, but haven't had much luck as of yet. I'm hoping the experts on this forum may be able to give me some guidance.

I'm trying to execute a Query to a database (via Microsoft Query), but I would like to 'filter' the Query based on a list of items that I'm looking for. I need to filter the Query itself rather than just the results of the query as the database can be quite massive and processing the hundreds of thousands of lines the Query would return with formulas tends to kill computers and time.

Basically, my situation is this:
My company uses an ERP database. I can connect Excel to this database via Microsoft Query.
I have lists of parts that I need to pull information for from this database (the database has hundreds of thousands of parts in it). I then need to use the information on the parts I'm looking for in various logic statements to identify/predict various things.

I know there are specific reporting solutions that can query the database based on input lists (my company uses Crystal Reports), however these solutions tend to be limited in how you can manipulate the data after it's been pulled, so I often have to resort to excel for post-processing of the data. I find the process of running a report, copy/pasting the data into excel, then using that static list as the excel data source a bit cumbersome, repetitive, and not entirely user-friendly. If I could import the data directly to Excel itself, that would be the ideal solution.

Hope you guys can help me out! Thanks!

[NeedForExcel]

So basically you want to use MS Query with a where clause that is dynamic?

[Fearra]

Sort of. I'm aware you can add parameters to a query and have the parameter reference a single cell in the workbook (making it dynamic). I'm looking for something that would have the same effect as adding a parameter that could reference a whole column in a table of the workbook.

For Example:
I've got a list of parts in a table in my workbook.

I want to be able to query one of the database tables, but only return rows where column 'Part_ID' is equal to one of the values on the list of parts.

[NeedForExcel]

Haven't tried this before, but I guess you will have to use ADO with your Query having the "IN" Operator with your list of parts stored in an array..

[NeedForExcel]

Hi, I just tried this out and it works

Please Login or Register  to view this content.

-------------------------------------------------
And if it is a Excel Table, replace this line

R = Application.Transpose(Cells(1, "J").CurrentRegion)

with

R = Application.Transpose(ActiveSheet.ListObjects("Table1").DataBodyRange)

-------------------------------------------------
And if it is a normal list with Headers, replace this line

R = Application.Transpose(Cells(1, "J").CurrentRegion)

with

R = Application.Transpose(Range("J2:J" & Cells(Rows.Count, "J").End(xlUp).Row))