Alternative to using FIND

[adambc]

I have 78 Workbooks - some small, some large - all of which have an “ID” column …

For a given ID, I need to generate a list of the files that have at least one row (record) for that ID …

I have a VBA routine which …
- opens each of the 78 WBs in turn
- gets the ID column number
- using FIND identifies whether the ID exists in that column (FINDs first occurrence or not at all)
- if the ID exists …
- writes out the filename to a list
- closes the WB
- moves on to the next WB
- if the ID doesn’t exist (but to get to that point FIND has to look at every row)
- closes the WB
- moves on to the next WB

The routine works “perfectly” ie I get a list of WBs that contain at least one row for the ID, except it takes rather a long time to complete for each ID …

Is there an alternative - quicker - to FIND (which of course needs to open the WB it’s looking in) to achieve the same thing?

Thanks for any help you’re able to give me …

 

[MikeVol]

Yes, you can speed up the process considerably by using ADO (ActiveX Data Objects) or DAO (Data Access Objects) to avoid opening each workbook manually in Excel. This will allow you to work with the files as a database and execute SQL queries against them.

 

[adambc]

Yes, you can speed up the process considerably by using ADO (ActiveX Data Objects) or DAO (Data Access Objects) to avoid opening each workbook manually in Excel. This will allow you to work with the files as a database and execute SQL queries against them.

Thanks

Both new to me

Will research