Searching a Closed Workbook ?

[Logit]

How would I modify the code below to search a closed workbook, in the same directory as the open workbook (that has the search form) ?

In the closed workbook, always searching COLUMN A on worksheets #2 & #3, returning data from columns A-J.

Wanting to keep the closed workbook closed at all times if possible (or at the least, hidden).

Thank you for your assistance.

Please Login or Register  to view this content.

[JapanDave]

I don't think you can search a closed book. You could open it discretely and do it that way.

[Logit]

I'll have to make concessions then.

Can you open a closed workbook but have it VISIBLE = FALSE ?

In any case, how would I modify the existing code to accomplish my goal (second workbook open or closed) ?

[Logit]

Or open it minimized ?

[FDibbins]

Why not just use a regular formula like vlookup?

[Logit]

I didn't want to reinvent what I already have.

I can't get the syntax working, changing "active workbook" to "workbooks" (referring to the closed workbook).

[buran]

You can use ADODB and SQL. Search the forum, there are many examples

[Logit]

#1 Anyone have suggestions to modify my existing code ?

#2 I don't think VLOOKUP would be wise 'cuz there are just over 1 million entries to search.

#3 ADOB and SQL are way above my understanding at this time. However, if anyone is kind enough to help, I'd be mighty appreciative !
I'm willing to learn if you are willing to explain.


Thank you all for your assistance.

[Kenneth Hobson]

Your code is incomplete. Where is the FindAll() routine? Chip Pearson has a good one. http://www.cpearson.com/excel/findall.aspx

There are several ADO methods out there. Ron de Bruin has a routine that can be handy. You would want to modify the SQL I suspect. Since I don't know what your goal is, I can not suggest an SQL string. http://www.rondebruin.nl/win/s3/win024.htm

[Logit]

Ken:

I've attached the project here.

I did originally leave off the code that is in the Module. Sorry about that ... thought just the UserForm code would be sufficient.

This project is based on Chip Pearson's example.

I managed to get it working for a short time when there was only 20 or so rows of data, on WorkSheet2 of this same WorkBook. When complete there will be just over 1 million rows of data to be searched in a separate workbook, always on sheets 2 and 3. Always searching column A of those two sheets.

Speed in returning the data of the search is paramount, as is using a closed workbook where the data will be stored.

I've played around with some code to try and hide the second workbook or at least make it invisible while the search is happening. Not alot of success there either. Haven't tried getting the data workbook (#2) minimized (another option).

I'm looking forward to learning from your suggestions. I'm a newbie with VBA. I've learned alot in the past month, but not enough to get me where I want to go with this project.

Thank you for your interest.