most efficient way to filter out and iterate through XLS databases

[Bart Wouters]

I just noticed posting this message in the wrong discussiongroup *sorry* ...
so here it is in the correc place)

Hi all,

I am currently doing some rapid prototyping in VBA-Excel2003.
Excel is used as lightweight relational database, hence requiring
frequent selection of 'records/rows' based on multiple criteria.

The selections then are transformed into arrays in order to present them in
listboxes.

What is the most efficient/performant way to filter out the relevant
information ?
autofilter ? advancefilter ? find(next) ? for each r in rows - loop ?

tnx in advance,
kind regards,
Bart Wouters

 

[RB Smissaert]

As you are talking about proto-typing a relational database, maybe the
best is to use SQL.

RBS

 

[Bart Wouters]

although an SQL database would indeed be the easiest way to work, the
operational environment I'm working in does not allow this kind of
prototyping on databases (not even access), the only tool at hand is excel
....

so I'll have to do my thing in excel, with tables = sheets, rows = records ...

 

[RB Smissaert]

You can use SQL in Excel.
If you do a Google NG search for this NG with my name and SQL you
will find some examples.

RBS