Searching Text Files in a Directory

I have several years of vendor invoices, in text file format, in some directories on a share. I need to search through these text files to find an order number, manifest number, or some other piece of information. I can’t search everything because it would take too long. And I don’t have control over the server, so if there is some indexing that could be done, I can’t do it. I’m stuck with good old VBA.

The folders are yyyymmdd (ex: 20150725 for July 25th) and corresponds to the invoice dates for any invoices in the file. Each file starts with a three letter abbreviation of the vendors name. Invoice date and vendor name are the only two pieces of information I can use to limit the search. The final piece of information is, of course, the search term. Here’s what the form looks like

I have a table of vendors and codes to populate the Vendor combobox. The QuickDate combobox populates the Date Range textboxes and contains common date ranges, namely, Last Month, This Month, Last Quarter, This Quarter, Last Year, This Year. I can change the dates to whatever I want if there isn’t a Quick Date that suits me. The Search Terms textbox takes a space separated list of terms to search for.

And now the fun part. The code. This converts the Quick Dates into real dates

This makes sure a real date is entered, but provides for 6 or 8 digit date entry.

And the big one, the actual search. This is pretty long and needs to be refactored, but it works for now.

It takes about 60 seconds per month to search the files. That’s a long time so it’s necessary to entertain the user while he waits. The top entry in the results listbox is whatever the current file is. It rapidly changes the display as it loops through the folder. When there’s a hit, that file becomes the second entry and any prior hits move down. This little animation lets the user know that it’s still working and gives him a list of what hits have been found already.

You can download SearchTextFiles.zip

7 thoughts on “Searching Text Files in a Directory

  1. I hadn’t considered that. I installed PQ in Feb of 2014 but uninstalled it shortly thereafter. Every day a little window would popup asking me to make PQ better, but what it really did was remind me that I was using computer resources to load something that I never use. Probably not what MS had in mind with that annoying reminder. Anyway, I’d be interested to see how that would work and what the results look like. If anyone wants to bite that off and doesn’t have somewhere to put it, I can put it up here.

    I thought about parsing everything periodically and putting it into SQL Server. We don’t add that many new ones and once it’s parsed, it’s parsed. But I’d have to write a parser for 20 different formats and I don’t want to.

  2. A simplewr & probably faster search routine.

  3. the initialize event can be written as:

  4. Whenever I have to search folders for a text snippet, I’m amazed at how fast & good Agent Ransack (www.mythicsoft.com) can parse through directories of information and find the needle I need. I can’t customize it in VBA, but it just works.

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax