I have an Excel workbook that contains a list of part numbers, anything from
a few to thousands, that need to be priced. A macro opens a connection to an
Access database of ALL known part numbers and gets ALL the prices, which upon
receipt at the Excel end are loaded into an array for further processing
(using the array gets me around the 65535 row limit, as we have ~300,000 part
numbers). There's also some If-Then wizardry that's performed by the SQL to
select the appropriate price from one of three possibilities.

The processing time to pull all the prices for every run is tolerable, but
if possible I'd prefer to set up the process so only the prices for the parts
in my current Excel list are be pulled. I experimented with a loop that runs
each part in the current list as a separate SQL query, but the run time for
that was far longer than just pulling all prices.

I need to figure out a way to use my list of parts as if it were an Access
table, so I can structure my SQL to only pull the prices I'm currently
interested in, rather than the whole 300K prices. I know exactly how I would
do it if I were working in Access, but this has to start from and run in an
Excel environment -- users may not even have Access installed on their PCs.
Who can set me on the right path?

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