Search multiple workbooks

[Mary]

We have multiple workbooks on a shared drive maintained by different
workgroups. There are times when a user will need to search several
workbooks before finding what they need. Is there an easier way to search
them all at once? I'd like to use Access for this, but importing all of the
data would be quite a project, and then the other users would need access to
make their updates. Thanks, Mary

 

[Bob Flanagan]

Have you tried going to the folder in Windows Explorer and doing a search?
One of the search options allows you to specify a search for a word or
phrase in a file.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Mary]

I think that may be the way to go. Right now the spreadsheets are not all in
the same folder because of the different workgroups. I may need to request
that they put them all in one so we can do these searches quicker. Thanks
for the advice.

 

[Mary]

What about code that would copy each of the spreadsheets at a given time each
day into the new folder I set up?

 

[Bob Flanagan]

Mary, I would not keep multiple copies in different locations. That is just
asking for trouble. It is a bit complex, but a macro can be written to scan
workbooks in different directories for specific text. Please give me a call
at 302-234-9857 and we can discuss.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Mary]

Bob, I appreciate your suggestion, and may give you a call if what I'm trying
now doesn't work out. I linked the various spreadsheets into an Access
database and am in the process of setting up a search feature that will dip
into each one. The problem is the spreadsheets aren't mine, but I have
access to them as a resource. Your macro to search them all would be great,
but I'm well into this method, so will see how it goes...

Thanks!