How about creating a WHERE clause along the lines of:
PartNumber IN ('12345', '23456', ..... )
and using that in your query?

Code would be something like this:
Dim C As Range
Dim stWhere As String
For Each C In Range("PartNumbers").Cells
stWhere = stwhere & "'" & C.Value & "', "
Next
stWhere = " PartNumber IN (" & Left(stWhere, Len(stWhere)-2) & ")"

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

That looks promising, if I can build a string long enough to hold,
potentially, thousands of part numbers. (Most of the time it'll be in the
3-digit realm, I think, but there's always the worst case to consider.)
Thanks, Bill, I'll re-post after I try it.

"Bill Manville" wrote:

How about creating a WHERE clause along the lines of:
PartNumber IN ('12345', '23456', ..... )
and using that in your query?

Code would be something like this:
Dim C As Range
Dim stWhere As String
For Each C In Range("PartNumbers").Cells
stWhere = stwhere & "'" & C.Value & "', "
Next
stWhere = " PartNumber IN (" & Left(stWhere, Len(stWhere)-2) & ")"

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Finally got that to work, Bill, after weeding out some of the inevitable
"what goes inside the parentheses and what doesn't" problems I always
encounter with embedded SQL. ;)

It does offer faster performance, though not as much as I hoped. Running
against a small list of part numbers (~250), it took just over one minute to
pull the specific prices, while running the old way that pulls all available
prices over into Excel (~430,000 at the moment) took about twice that. I
plan to keep testing it against ever-larger lists of parts to see where the
"break-even" point is. In any event, you've moved me forward; thanks again.



"Bill Manville" wrote:

How about creating a WHERE clause along the lines of:
PartNumber IN ('12345', '23456', ..... )
and using that in your query?

Code would be something like this:
Dim C As Range
Dim stWhere As String
For Each C In Range("PartNumbers").Cells
stWhere = stwhere & "'" & C.Value & "', "
Next
stWhere = " PartNumber IN (" & Left(stWhere, Len(stWhere)-2) & ")"

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Oh, by the way, I did some Help-surfing before trying this, and discovered
that a string variable can hold a billion-plus characters, so that solved my
initial concern.

"Bill Manville" wrote:

How about creating a WHERE clause along the lines of:
PartNumber IN ('12345', '23456', ..... )
and using that in your query?

Code would be something like this:
Dim C As Range
Dim stWhere As String
For Each C In Range("PartNumbers").Cells
stWhere = stwhere & "'" & C.Value & "', "
Next
stWhere = " PartNumber IN (" & Left(stWhere, Len(stWhere)-2) & ")"

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Pleased you got it working.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup