Date search and date extract

[joecrabtree]

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

 

[Bob Phillips]

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]

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

 

[Jay]

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

 

[Bob Phillips]

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)