Find word files for occurances of 1000 set strings 4

[Fenrirshowl]

Hi guys

Here's one to get your brains around and multiple appreciation stars will be on offer!

Problem:

I have a 1000 strings to uniquely identify a list a people (currently held in excel or access). These people are a subset of approximately 20,000 people.

I need to search all of the word documents in a folder and pull out those that contain any of the 1000 strings. The total number of word documents that need to be review is "significant" - it will effectively be every letter or document written over a six year period to/or relating to any of the 20,000 population.

Time is also a factor as this needs doing within a week.

Doing this manually will therefore prove next to impossible and I will need to automate (hence the VB forum).

There will be several folders that need to be reviewed, with non standard locations, so I think defining the folder location by prompt will be required.

Opening each word document will obviously slow everything down but I do not know if you can perform these operations on a closed Word doc.

The files are on a separate server in another office - accessing files on these other servers is usually slow so any thoughts on minimising this problem will also be appreciated. If it's simple enough, I can email it to the other office for someone else to run to eliminate this issue.

Given the problem, I need far better code that I can currently write. IF I could get close I am sure that it would grind to a halt so please treat me as a complete novice on this one.

Thanks in advance

Fen

 

[SBendBuckeye]

How long did it take to execute and approximately how many files were searched? Thanks!

Have a great day!

j2associatesNO_SPAM@gmail.com

 

[Fenrirshowl]

The test I have set up is hardle worth mentioning - I simply wanted to make sure the code worked.

I will be using it at the start of next week (hopefully if I get provided the proper authorisations to access the other office's drive) and can provide some feedback then if you are interested. Alternatively you should be able to derive the table designs (2 only, one with one "testwords" field in the "Words" table and the second with 5 fields (ID, Document, Description, Filelocation, Edate) from the code and set up your own version in a couple of minutes.

Seems a handy piece of kit for a number of situations though, so I'm lad I've got it.

Fen

 

[JerryKlmns]

Fen,

I should had mentined also that, since your are planning to use it on 20,000 PCs, maybe it would be proficient to include some info about the user + PC names

    Dim objWscript As Object
    Set objWscript = CreateObject("Wscript.Network")
    TheComputer = objWscript.ComputerName
    UserDomain = objWscript.UserDomain
    UserName = objWscript.UserName
    Set objWscript = Nothing

plus the last modified date of the document (check DateLastModified Property in help, there is an example with more infos about the file). And what about servers? Should a user be alowed to choose a mapped drive letter? Then you should retrieve the UNC and not the mapped letter! (This is left as an excercice for the reader)

And don't forget the 1MB max table limit size!


---------
Thank you very much, for your kind words.

 

[Fenrirshowl]

Thanks again Jerry - I didn't know about the 1Mb max table size, but hopefully that won't be a problem.

I don't think I need the most recent bit of code as I think there was a bit of a misunderstanding: I am looking for information on 1,000 members of a 20,000 member population. Each member will have a certain number of letters associated with them, say 6 on average. Therefore the number of files I am looking for is 6,000 from a total of 120,000.

I don't need to run this from 20,000 machines, just as many as it takes to get the job done.

Fen

 

[Fenrirshowl]

Initial results of the search routine:

Folder searched is 61Mb, containing 2380 files in 20 folders.

It took roughly 9.5hrs (from midnight to 9:30am) to search for 1800 strings within those files.

The search was performed on a local server (it proved quicker and easier to copy the folders across rather than try to search the other office's server.

Note that while running Task Manager says Access is not responding (which it isn't). Pressing Esc did not break the macro and the only way to stop it was to close Access via Task Manager.

The search continues....