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?