[RBASE-L] - Re: Getting a file from a directory

[Jason Kramer]

Karen,     If you are sure that there will only be one file per part number, and if the part numbers are unique, then the first bit of VB code below will search for a file beginning with <PARTNO> and put it into a file.     If you also want to extract the revision number, and you know that there will only be numbers between the final R (regardless of how many Rs there are in the part number itself) and the . in the file name (there there will be only on period in the file name), then the second code sample below will output a single line to a CSV file with the full file name,revision number. _vbscript_ 1 On Error Resume Next Dim fso,folder,files,NewFile,sPartNo,sCurFName,iPartNoLen Set fso = CreateObject("Scripting.FileSystemObject") sPartNo = Wscript.Arguments.Item(0) If sPartNo = "" Then     Wscript.Echo "No sPartNo parameter was passed"     Wscript.Quit End If Set NewFile = fso.CreateTextFile("FileList.csv", True) Set folder = fso.GetFolder(".") Set files = folder.Files iPartNoLen = Len(sPartNo) For each folderIdx In files     sCurFName = folderIdx.Name     If Left(sCurFName,iPartNoLen) = sPartNo Then         NewFile.WriteLine(sCurFName)     End If Next NewFile.Close _vbscript_ 2 On Error Resume Next Dim fso,folder,files,NewFile,sPartNo,sCurFName,iPartNoLen Dim iStartPos,iEndPos,iRlen Dim sRVal Set fso = CreateObject("Scripting.FileSystemObject") sPartNo = Wscript.Arguments.Item(0) If sPartNo = "" Then     Wscript.Echo "No sPartNo parameter was passed"     Wscript.Quit End If Set NewFile = fso.CreateTextFile("FileList.csv", True) Set folder = fso.GetFolder(".") Set files = folder.Files iPartNoLen = Len(sPartNo) For each folderIdx In files     sCurFName = folderIdx.Name     If Left(sCurFName,iPartNoLen) = sPartNo Then         sCurFName = UCase(sCurFName)         iEndPos = InStr(1,sCurFName,".",0) - 1         iStartPos = InStrRev(sCurFName,"R",iEndPos,0) + 1         iRLen = iendPos-iStartPos + 1         sRVal = Mid(sCurFName,iStartPos,iRlen)         NewFile.WriteLine(sCurFName & "," & sRVal)     End If Next NewFile.Close Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) On 2/9/2011 4:57 PM, karentel...@cs.com wrote: Jason:   I'll certainly look over your vb code in detail later!  Love analyzing vb code. For my situation, there will always be only ONE file out there for each part, never multiple files with different revision numbers (client finally got back to me with that info).  And the part number can be both numbers and letters, so there will be multiple R's in the filename (such as:  "HammerR3.doc")....   For now, a simple while loop incrementing a number from 1 to 30 did the trick.  He said that revisions have never historically gone over 10, so we did 30 just to be safe. Karen I may have another solution.  I wrote a _vbscript_ file that will get a list of all the files for a given part number, find the revision number, write a CSV file with all the matching file names and revisions, loads the file into a temp table and then selects the max revision.  This way, you don't have to worry about maximum revisions.  If your customer suddenly decides to start revisions at 1000, you won't be stuck because you were counting backwards from 30.  Also, this gives you all revisions in a table so you can offer to show older revisions (depending on how much you like your customer, of course) This solution depends on the file names always being <partnum>R<revisionnum>.ext.  If the file name has more than one R, you will get very odd results. Please let me know if you want a sample DB.