Searching through folders/subfolders for a file (VBA/Office 2000)

Topic

New Reply

I’m trying to help a legal office to save e-mail messages from Outlook to their document folders, using code adapted from a procedure I’ve already developed for them for their Word documents. One feature that they wish to retain in Outlook is the ability to key in a matter number, and have the relevant matter folder automatically located among all the folders and subfolders below their Current Clients folder (there are hundreds, if not thousands, of folders).

In Word I use the FileSearch method, which isn’t fast but does the job. When I try to use Word.FileSearch from Outlook, however, it’s abysmally slow. Is there any other way of searching for files? I would have thought that if FileSearch is an Office method (which is indicated by the Help documentation), there might be a variation that relates to searching at the Windows level.

I’ve spent hours looking through manuals in the computer bookshops, but haven’t found what I need. I’ve also checked the last 20 pages of VB/VBA postings in the lounge, but with no search feature and a slow dial-up connection, it’s difficult to know if there might be a posting lurking further back that I should use!

Thanks and regards

Reply | Quote

Replies

Hi Alison,
I must confess I’m a little unclear as to exactly what you want to do with the folder in question. When you say “have the relevant matter folder automatically located ” do you mean you want it created, or displayed, or to change to that directory for saving, or something else entirely?
One immediate thought is that you should try using Application.FileSearch rather than Word.Filesearch from Outlook or use the Scripting.FileSystemObject.
Hope that helps.

Reply | Quote

Hi Rory –

Sorry I didn’t make it clear. What I want the user to be able to do is to key in the matter number, and have a routine that will find the full path to the matter folder, so that the selected message can be saved to a subfolder within that folder. The structure is based on a folder called Current Client Folders, with subfolders for each legal practitioner, below which there are folders for each matter. These in turn have subfolders for different types of document associated with the matter. The person saving the message might not be the practitioner controlling the matter. Once the matter folder has been located (there can be hundreds of matters per practitioner), the Save As dialog box (which I’m OK with) should display that matter folder, so that the operator can select the subfolder for storing the message. Hope that makes it clearer.

I couldn’t get Application.FileSearch to work in Outlook, and I felt the the Scripting.FileSystemObject would be too slow because I’d have to write a recursive loop, which was causing me some problems. I’ll look through the ideas below, though, and may find that’s the way to go.

Thanks for your interest.

Reply | Quote

Windows really has no choice but to check every path until it finds the folder. You might want to create some kind of index that stores the correct path for each matter. Presumably this can be found among the data that could be exported from the accounting system and imported into an Access table. Using ADO from inside Outlook, you then could have very fast queries to find the folder you want. Is it all worth it? I don’t know…

Anyway, I don’t know the equivalent Windows API code to implement FileSearch natively. It might be marginally faster. How are you limiting FileSearch to folder names??

We are using WORLDOX from World Software, which is reasonably affordable for small law offices and offers integration with Outlook. Something to consider if the hair pulling becomes too painful.

Reply | Quote

I wrote you some code using the FileSystemObject, which is part of the Microsoft Scripting Library (installed with Internet Explorer, I believe). Assuming this library is on your users’ PCs, this might run faster than Application.FileSearch. Would you be willing to benchmark it using some sample searches in your environment? I’m attaching a module you can import, after you change the file name back to .bas. It is coded to use late binding, so no additional Reference needs to be set. You can switch it back to early binding, as you’ll see in the code. Hope this helps.

Reply | Quote

(Edited by MarkD on 02-Aug-03 20:17. Revised text file attached – fixed few minor bugs & typos.)

The MS Scripting Library should be available on pc w/any “recent” version of Windows (Win 98 or later). Just for fun (??) compared doing a file search using (1) Windows API functions; (2) intrinsic VB statements; and (3) FileSystemObject (FSO) methods. In my limited testing, in almost all cases, using old fashioned VB commands and/or FSO beat out the API functions (which, however, can return more detailed file information if necessary). Some sample test results:

TestFileSearch “C:Windows”, “*.ocx”, 1
API:
55 Files found in 714 Directories
Elapsed Time: 3 seconds.
VB:
55 Files found in 714 Directories
Elapsed Time: 2.657 seconds.
FSO:
55 Files found in 715 Directories
Elapsed Time: 2.5 seconds.

TestFileSearch “C:Program Files”, “*.exe”, 1
API:
1065 Files found in 5063 Directories
Elapsed Time: 20.672 seconds.
VB:
1065 Files found in 5063 Directories
Elapsed Time: 16.797 seconds.
FSO:
1065 Files found in 5064 Directories
Elapsed Time: 24.937 seconds.

TestFileSearch “C:”, “*.mdb”, 1
API:
508 Files found in 8016 Directories
Elapsed Time: 88.562 seconds.
VB:
508 Files found in 8014 Directories
Elapsed Time: 43.531 seconds.
FSO:
508 Files found in 8014 Directories
Elapsed Time: 38.765 seconds.

TestFileSearch is a sub that tests (and times) the various methods used for file search (1st arg is path to start search, 2nd arg is what to search for). Note if searching the entire hard drive (C:) the difference in search time is significant. Obviously, if you can narrow down search to a specific folder or folders you can speed things up. See attached text file for sample code, which is way too lengthy (esp. the API’s) to post here. Some code was adapted from MSKB, articles are cited. Using some of these methods may be speedier than Application.FileSearch (which I haven’t tried). My preference for this type of thing is to use FSO – unless you like doing things the hard way….

HTH

Reply | Quote

Wow, that’s great. You probably could get this sort of thing published in a VB newsletter. Although nowadays they’d probably fault you for not testing a .NET implementation. Anyway, it puts my mind at ease that I’m not learning all those API calls.

Reply | Quote

Hi MarkD –

Thanks for going to the trouble of offering the various options with comparison data. It looks as though I was barking up the wrong red herring in my search for an API solution, and what had seemed to be my last choice (FSO) will probably turn out to be my saviour!

I’ll check out both your solution and jscher’s, and see what seems to fit best, before reporting back.

Reply | Quote

Hi jscher –

Thanks for the code – it’s along the lines of something I’d started to try with the Dir function, but I was having trouble with the recursion. Like MarkD, I’d then planned to use the timer to compare with the original method. I’ll give yours a go, and the fastest of MarkD’s, and see what returns the best result.

In reply to the points made in your earlier posting – I’m not actually working in with their accounting software (they just use the matter numbers as an existing reference) and I think that the Access index would require more time and effort than they want to expend! I’ve come across Worldox from time to time, and find it cramps my style a bit when I have other coding to do, but that’s probably just because I don’t know it well enough. I hadn’t found a way to get FileSearch to look for folders, so actually have a dummy file created as a placeholder whenever they create a new matter folder (come to think of it, if we did want to go with the Access procedure, that’s probably the point at which I’d record the data, but I’d also have to create a routine to allow for moving and deleting folders, which isn’t a major issue with the dummy file process.)

Anyway, thanks again for your help, and I’ll report back when I have some test results.

Reply | Quote

Just as a footnote, my code looks only at folders, not files at all. Mark’s might be more general purpose if you need a single function that can do both.

Regarding WORLDOX, yes, they really need to update all their macros from WordBasic to VBA. Maybe some people are still using Word95, but it can’t be all that many, and the code is really confusing right now. And the SDK documentation is weak. (And the SDK support seems nonexistent.) But from the non-developer angle, it’s a fairly good set of compromises.

Reply | Quote

I’m fine with code that just looks at folders – I imagine it’ll be more efficient, and if all goes well I’ll incorporate it into the Word side of the solution to give them extra speed there, and then I can ditch all the placeholder files, which the client will be quite happy for me to do (I’ll probably vary the procedure to enable me to automate the deleting of those files!).

I didn’t get to test the code over the weekend, which tends to be overloaded with family activities, and have other client appointments today, so will be getting down to it this evening and hope to have some idea which way to go by the end of the day!

Thanks again

Reply | Quote