I have a problem with trying to find specific text within a set of txt
files. I have a directory with 15 generic text files in it. I am
supplied with a very unique number that I have to match to one of the
text files, however I don't know which one of the 15 it may be. I'm
not referring to the filename.txt file, but a string of text within
the text file. The filename.txt file contains my unique number inside
it.

Is there an easy VBA way to scan the 15 txt files and open the one I
want based on the number I'm given, open the text file to pull the
data from it into a spreadsheet?

Once I get this figured out - I have to do the exact same thing to a
spreadsheet file. The same unique number I'm give is also associated
with a spreadsheet in a directory (along with many others). I'll
need to open that spreadsheet and extract the data.

The commonality is the unique number inside the files. Any help or
pointers would be appreciated.

-Tony

Do you have to do this once or more than once? If more than once, how often?

"Tony" wrote in message
om...
I have a problem with trying to find specific text within a set of txt
files. I have a directory with 15 generic text files in it. I am
supplied with a very unique number that I have to match to one of the
text files, however I don't know which one of the 15 it may be. I'm
not referring to the filename.txt file, but a string of text within
the text file. The filename.txt file contains my unique number inside
it.

Is there an easy VBA way to scan the 15 txt files and open the one I
want based on the number I'm given, open the text file to pull the
data from it into a spreadsheet?

Once I get this figured out - I have to do the exact same thing to a
spreadsheet file. The same unique number I'm give is also associated
with a spreadsheet in a directory (along with many others). I'll
need to open that spreadsheet and extract the data.

The commonality is the unique number inside the files. Any help or
pointers would be appreciated.

-Tony

Here is a simple example of code that calls the operating system utility
"findstr" (that might ordinarily be used at the command line or in batch
files). I am using Win2K, but most recent Windows OS's have findstr or the
similar, but less flexible 'find.' The problem with this approach is that
Shell does not wait for findstr to finish. The next line of code will likely
execute before findstr has found the file. That is why I added the three
second delay ( Do While Timer() - t0 < 3: Loop ). If you use this, you will
have to guess how long of a delay would be adequate. To learn more about
findstr, see Windows help or open a command window and type findstr /?.

Option Explicit

Sub Main()
Dim a As String, t0 As Single
t0 = Timer()
'looking for 234 in files like f*.txt in K:\t1
Call Shell("cmd /k findstr /p 234 k:\t1\f*.txt k:\t1\found.txt")
Do While Timer() - t0 < 3: Loop
Open "k:\t1\found.txt" For Input As #1
Do While Not EOF(1)
Input #1, a
Debug.Print a
Loop
Close 1
End Sub

(If you want to know a more certain, but more complicated way to decide when
a shelled process finishes, look he
http://support.microsoft.com/default...NoWebContent=1
for "How To Use a 32-Bit Application to Determine When a Shelled Process
Ends.")

If you know the format of the text files well enough, you could just open
each one and look for the word, using the Open, Do-Loop, Close approach on
each file. I suggest that you look around the internet for code already
written with this approach in mind. You will need a routine to return each
file in a directory, and a routine to open, read and close each file, in
turn until you find the one you want.

As for the workbooks, the standard search tool in Win2k and XP
(Run Search For Files and Folders) will find text in worksheets and Word
documents. Can't tell you how to automate it off the top of my head.

Again, if you want to code the workbook search, put together code you can
find on line for opening multiple workbooks in turn, looping through the
worksheets, searching for text.

If you don't need to code this, don't. Use existing tools,like the Windows
search tools I mentioned. Many better text editors can search multiple files
on a drive for arbitrary text. UltraEdit is one. (www.ultraedit.com). You
can download a copy and use it free for a limited time.

"Tony" wrote in message
om...
I have a problem with trying to find specific text within a set of txt
files. I have a directory with 15 generic text files in it. I am
supplied with a very unique number that I have to match to one of the
text files, however I don't know which one of the 15 it may be. I'm
not referring to the filename.txt file, but a string of text within
the text file. The filename.txt file contains my unique number inside
it.

Is there an easy VBA way to scan the 15 txt files and open the one I
want based on the number I'm given, open the text file to pull the
data from it into a spreadsheet?

Once I get this figured out - I have to do the exact same thing to a
spreadsheet file. The same unique number I'm give is also associated
with a spreadsheet in a directory (along with many others). I'll
need to open that spreadsheet and extract the data.

The commonality is the unique number inside the files. Any help or
pointers would be appreciated.

-Tony

Look at the filesearch object. It allows you to look for a string within a
file.

--
Regards,
Tom Ogilvy

"Tony" wrote in message
om...
I have a problem with trying to find specific text within a set of txt
files. I have a directory with 15 generic text files in it. I am
supplied with a very unique number that I have to match to one of the
text files, however I don't know which one of the 15 it may be. I'm
not referring to the filename.txt file, but a string of text within
the text file. The filename.txt file contains my unique number inside
it.

Is there an easy VBA way to scan the 15 txt files and open the one I
want based on the number I'm given, open the text file to pull the
data from it into a spreadsheet?

Once I get this figured out - I have to do the exact same thing to a
spreadsheet file. The same unique number I'm give is also associated
with a spreadsheet in a directory (along with many others). I'll
need to open that spreadsheet and extract the data.

The commonality is the unique number inside the files. Any help or
pointers would be appreciated.

-Tony

Thanks Tom - this is the direction I needed. I was able to use the
help (imagine that) to find out how to use the Application.Filesearch
in a manner listed below. I had never used Filesearch, so wasn't
familiar with it being available.

Code:

myPath = "\\network\Directory Name\Subdir Name\"
eGress = 0

Sht2RowCnt = 2
Label1.Caption = "Retrieving Data..."
Worksheets("Sheet1").Range("c2:al200").ClearConten ts
Worksheets("Sheet1").Range("df2:dk200").ClearConte nts
Worksheets("Sheet1").Range("bj2:CJ200").ClearConte nts
Worksheets("Sheet2").Range("a2:z200").ClearContent s
DoEvents

For x = 2 To 5000
If Trim(Sheet1.Cells(x, 1)) = "" And Trim(Sheet1.Cells(x, 2)) = ""
Then
Exit For
Else

With Application.FileSearch
.NewSearch
.LookIn = myPath + Trim(Sheet1.Cells(x, 2))
.TextOrProperty = Trim(Sheet1.Cells(x, 1))
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
intFilesCount = .FoundFiles.Count

For i = 1 To intFilesCount
Next i
myfile = .FoundFiles(intFilesCount)

End With

Open myfile For Input As #1
.....

this allowed me to find the one file I needed without having to know
the filename. For the code record - my Sheet1.Cells(x, 1) contains
the unique number, Sheet1.Cells(x, 2) contains the folder name. I can
have as many as 200 rows of numbers. As you're probably guessing -
these txt and excel files I'm dealing with are "big equipment"
software generated. So in order to get the data, I use an automation
method rather than typing it all in by hand (yikes).

Bob - I tried your code as well, however I could not get the findstr
command to function with network devices. It worked GREAT locally,
but not at all via a network. I'll keep the code for future reference
tho. Thanks for the lengthy reponse and explanation!!

-Tony


"Tom Ogilvy" wrote in message ...
Look at the filesearch object. It allows you to look for a string within a
file.

--
Regards,
Tom Ogilvy