Date search and date extract
 

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_Po sition,Clamp_Pressure,Current_Job,Toolslide_Positi on,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

Date search and date extract
 

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" wrote in message
...
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_Po sition,Clamp_Pressure,Current_Job,Toolslide_Positi on,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

Date search and date extract
 

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

Date search and date extract
 

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

Date search and date extract
 

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" wrote in message
...
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