To all,

I have a folder containing text files with a date as the title
proceded by RD. For example RD071129, RD 071228 ( Format YYMMDD). In
each text file I have data in the same format as below:

Date,Time,Pierce_Position,Pierce_Pressure,Clamp_Position,Clamp_Pressure,Current_Job,Toolslide_Position,Press
Mode,Rotary 1 Furnace Temperature,Rotary 2 Furnace Temperature
2007/29/11,00:04:42,1298.,42.,10,1182.,36075,1,Manual,0,0
2007/29/11,00:04:42,1298.,42.,10,1193.,36075,1,Manual,0,0
2007/29/11,00:04:43,1298.,42.,10,1199.,36075,1,Manual,0,0
2007/29/11,00:04:43,1298.,42.,11,1205.,36075,1,Manual,0,0
2007/29/11,00:04:44,1298.,42.,11,1210.,36075,1,Manual,0,0
2007/29/11,00:04:44,1298.,42.,10,1215.,36075,1,Manual,0,0
2007/29/11,00:04:45,1298.,42.,10,1220.,36075,1,Manual,0,0
2007/29/11,00:04:45,1298.,42.,10,1226.,36075,1,Manual,0,0
2007/29/11,00:04:46,1298.,42.,10,1231.,36075,1,Manual,0,0
2007/29/11,00:04:46,1298.,42.,10,1237.,36075,1,Manual,0,0
2007/29/11,00:04:47,1298.,42.,11,1242.,36075,1,Manual,0,0
2007/29/11,00:04:47,1298.,42.,11,1248.,36075,1,Manual,0,0
2007/29/11,00:04:48,1298.,42.,11,1254.,36075,1,Manual,0,0
2007/29/11,00:04:48,1298.,42.,10,1260.,36075,1,Manual,0,0


The important part of this data is the job number. In the above data
example the job number is 36075. Is there any way that I can search
these text files for a user specified job number, and then display a
list of all the file names ( Dates ) that this job number occured on.
For example if I searched 36075 it would return 20072911 etc?

Thanks in advance for your help,

Regards

Joseph Crabtree

Joseph,

Isn't this a job for Windows Search?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"joecrabtree" <(E-Mail Removed)> wrote in message
news:e3b3ef12-4924-44ba-87d5-(E-Mail Removed)...
> To all,
>
> I have a folder containing text files with a date as the title
> proceded by RD. For example RD071129, RD 071228 ( Format YYMMDD). In
> each text file I have data in the same format as below:
>
> Date,Time,Pierce_Position,Pierce_Pressure,Clamp_Position,Clamp_Pressure,Current_Job,Toolslide_Position,Press
> Mode,Rotary 1 Furnace Temperature,Rotary 2 Furnace Temperature
> 2007/29/11,00:04:42,1298.,42.,10,1182.,36075,1,Manual,0,0
> 2007/29/11,00:04:42,1298.,42.,10,1193.,36075,1,Manual,0,0
> 2007/29/11,00:04:43,1298.,42.,10,1199.,36075,1,Manual,0,0
> 2007/29/11,00:04:43,1298.,42.,11,1205.,36075,1,Manual,0,0
> 2007/29/11,00:04:44,1298.,42.,11,1210.,36075,1,Manual,0,0
> 2007/29/11,00:04:44,1298.,42.,10,1215.,36075,1,Manual,0,0
> 2007/29/11,00:04:45,1298.,42.,10,1220.,36075,1,Manual,0,0
> 2007/29/11,00:04:45,1298.,42.,10,1226.,36075,1,Manual,0,0
> 2007/29/11,00:04:46,1298.,42.,10,1231.,36075,1,Manual,0,0
> 2007/29/11,00:04:46,1298.,42.,10,1237.,36075,1,Manual,0,0
> 2007/29/11,00:04:47,1298.,42.,11,1242.,36075,1,Manual,0,0
> 2007/29/11,00:04:47,1298.,42.,11,1248.,36075,1,Manual,0,0
> 2007/29/11,00:04:48,1298.,42.,11,1254.,36075,1,Manual,0,0
> 2007/29/11,00:04:48,1298.,42.,10,1260.,36075,1,Manual,0,0
>
>
> The important part of this data is the job number. In the above data
> example the job number is 36075. Is there any way that I can search
> these text files for a user specified job number, and then display a
> list of all the file names ( Dates ) that this job number occured on.
> For example if I searched 36075 it would return 20072911 etc?
>
> Thanks in advance for your help,
>
> Regards
>
> Joseph Crabtree

Yes you can do it that way, but I want to be able to integrate it into
an excel macro that I'm writing.

Regards

Joseph Crabtree

Hi Joe and Bob -

Bob, I couldn't get Windows Search to find the "phrase" 36075 when I
captured Joe's data in a .csv file on my hard drive. Any ideas why it
wouldn't work ? It sounds like a great way to solve Joe's problem.

In the meantime, Joe, here's an all VBA solution. The code below should be
copied to the worksheet module of a blank worksheet. Change the
'myFolderPath' statement as needed. The code executes whenever you enter a
job number in Cell A1 and it outputs the list starting in Cell A2:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value <> "" Then
Application.EnableEvents = False
Range(Cells(2, 1), Cells(Rows.Count, 1)).Clear
Range("B1") = "Searching. Please Wait..."
Target.Select
jobNumber = Target.Value
myFolderPath = "My Documents" '<---Change to suit

With Application.FileSearch
.LookIn = myFolderPath
.SearchSubFolders = True 'or False
.FileType = msoFileTypeAllFiles
.TextOrProperty = jobNumber
.Execute
For Each fl In .FoundFiles
ActiveCell.Offset(1, 0).Activate
fName = Right(fl, Len(fl) - InStrRev(fl, "\"))
If InStr(fName, ".") Then
fName = Left(fName, InStrRev(fName, ".") - 1)
End If
fName = Replace(fName, "RD", "20", 1)
ActiveCell.Value = fName
Next 'fl
End With

End If
Range("B1").Clear 'Erases status prompt
End If
Application.EnableEvents = True
End Sub
---
Jay



"joecrabtree" wrote:

> Yes you can do it that way, but I want to be able to integrate it into
> an excel macro that I'm writing.
>
> Regards
>
> Joseph Crabtree
>

That's correct Jay, it doesn't seem to work with csv files, but it does with
txt files.

BTW FYI FileSearch has gone in Excel 2007.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jay" <(E-Mail Removed)> wrote in message
news9FCBF3E-D979-4E26-9643-(E-Mail Removed)...
> Hi Joe and Bob -
>
> Bob, I couldn't get Windows Search to find the "phrase" 36075 when I
> captured Joe's data in a .csv file on my hard drive. Any ideas why it
> wouldn't work ? It sounds like a great way to solve Joe's problem.
>
> In the meantime, Joe, here's an all VBA solution. The code below should
> be
> copied to the worksheet module of a blank worksheet. Change the
> 'myFolderPath' statement as needed. The code executes whenever you enter
> a
> job number in Cell A1 and it outputs the list starting in Cell A2:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Target, Range("A1")) Is Nothing Then
> If Target.Value <> "" Then
> Application.EnableEvents = False
> Range(Cells(2, 1), Cells(Rows.Count, 1)).Clear
> Range("B1") = "Searching. Please Wait..."
> Target.Select
> jobNumber = Target.Value
> myFolderPath = "My Documents" '<---Change to suit
>
> With Application.FileSearch
> .LookIn = myFolderPath
> .SearchSubFolders = True 'or False
> .FileType = msoFileTypeAllFiles
> .TextOrProperty = jobNumber
> .Execute
> For Each fl In .FoundFiles
> ActiveCell.Offset(1, 0).Activate
> fName = Right(fl, Len(fl) - InStrRev(fl, "\"))
> If InStr(fName, ".") Then
> fName = Left(fName, InStrRev(fName, ".") - 1)
> End If
> fName = Replace(fName, "RD", "20", 1)
> ActiveCell.Value = fName
> Next 'fl
> End With
>
> End If
> Range("B1").Clear 'Erases status prompt
> End If
> Application.EnableEvents = True
> End Sub
> ---
> Jay
>
>
>
> "joecrabtree" wrote:
>
>> Yes you can do it that way, but I want to be able to integrate it into
>> an excel macro that I'm writing.
>>
>> Regards
>>
>> Joseph Crabtree
>>