| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EXTRACT MONTH FROM DATE | New Users to Excel | |||
| Extract Date | Excel Worksheet Functions | |||
| Extract Day of Week from Date in VBA | Excel Programming | |||
| extract name when a date in another cell gets near | Excel Discussion (Misc queries) | |||
| extract date from the most current date | Excel Discussion (Misc queries) | |||
Linear Mode
