Seeking Advice: Efficient VBA Data Lookup in Table?

[NigelTufnel]

I'm working on a project in which I have to do a large number of lookups (thousands) on a series of large Excel lists (thousands of rows, perhaps ten columns each). I've done a lot of VBA programming, but not so much with databases/lists.

For each lookup, I will set three or four criteria, and a single record will be returned (if the data is good). I need to extract around five pieces of information for that unique record.

What's the best way to do this using VBA if I'm interested in calculation speed?

I put some code together that used Autofilter, and it seemed to run fine last night (it returned a result quickly and accurately). For reasons I cannot explain, today it's taking 15 seconds to impose each criteria on the database, so each lookup takes a minute long. I'm running Excel 2007 in Windows 7.

Is using Autofilter the best way to accomplish this task?

Another possibility is to manually set up some columns that return TRUE/FALSE for each criteria and another column that takes the AND across each row. I could then simply search for a TRUE in the AND column--this has got to take less calculation time than the Autofilter currently takes, although it will require more prep effort.

Any suggestions?

Thanks.

 

[Fazza]

With large amounts of data, maybe use UPDATE queries? Such as via VBA & ADO. OK?

 

[tusharm]

The way I would approach this first is to treat the table as the source for a SQL query. Excel supports SQL queries through the MS Query add-in that is accessed via Data tab | Get External Data group | From Other Sources drop down | from Microsoft Query button. It's also available in 2003 or earlier versions though the access is obviously different.

MS Query support parameterized queries where the query result is refreshed automatically when a parameter changes. For an intro see
Building and using a relational database in Excel (with a little help from MS Query)
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

I'm working on a project in which I have to do a large number of lookups (thousands) on a series of large Excel lists (thousands of rows, perhaps ten columns each). I've done a lot of VBA programming, but not so much with databases/lists.

For each lookup, I will set three or four criteria, and a single record will be returned (if the data is good). I need to extract around five pieces of information for that unique record.

What's the best way to do this using VBA if I'm interested in calculation speed?

I put some code together that used Autofilter, and it seemed to run fine last night (it returned a result quickly and accurately). For reasons I cannot explain, today it's taking 15 seconds to impose each criteria on the database, so each lookup takes a minute long. I'm running Excel 2007 in Windows 7.

Is using Autofilter the best way to accomplish this task?

Another possibility is to manually set up some columns that return TRUE/FALSE for each criteria and another column that takes the AND across each row. I could then simply search for a TRUE in the AND column--this has got to take less calculation time than the Autofilter currently takes, although it will require more prep effort.

Any suggestions?

Thanks.

 

[Fazza]

Sorry, I mis-read the question. UPDATE queries are not suitable.

Parameterised queries, as per Tushar, might be fantastic. (Or similar via VBA if you want more control/flexibility.) It can be set up in a file separate to the data file: the data file can be mdb, xls, or whatever. If in the same file as the source data and the file name/path changes a little bit of VBA will help accommodate the changes (to the query connection and, if necessary, the SQL aka CommandText).

regards

 

[NigelTufnel]

Tushar and Fazza--

Thanks for the detailed and thoughtful suggestions.

One (perhaps critical?) piece of information that I left out...

I don't plan to do more than one lookup of a single table in succession.

The nature of the task dictates that I find a piece of data in each of the "N" databases in succession for a particular operation, then find a single piece of data in each database for the next operation, etc.

Does this negate the advantage of using SQL?

 

[Fazza]

It isn't clear to me what is required, Nigel. I guess an SQL-type approach will still be good.

It might help to have an example.