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

[Jason Kramer]

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. I'll put the VBS code and the RMD code that runs it below.  The VBS file has to go in the same directory as the part number files.  If this is not possible, it is easy enough to edit to take into account the file/folder structure of the target machine.  The folder can even be specified at runtime if it is going to change frequently. Special thanks to: http://www.go4expert.com/forums/showthread.php?t=927 for the initial outline of the VBS code.  I have heavily modified it, but the original file listing routine came from there. The CSV file will look like: 1234R1.DOC,1 234R34.DOC,34 VBS code: 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)         iStartPos = InStr(1,sCurFName,"R",0) + 1         If iStartPos = 1 Then             sRVal = ""         Else             iEndPos = InStr(1,sCurFName,".",0)             iRLen = iendPos-iStartPos             If iRLen = 0 Then                 sRVal = ""             Else                 sRVal = Mid(sCurFName,iStartPos,iRlen)             End If         End If         NewFile.WriteLine(sCurFName & "," & sRVal)     End If Next NewFile.Close RMD code SET VAR vgoodfname TEXT = NULL CREATE TEMP TABLE tflist (fname TEXT 50,rval INTEGER) DELETE filelist.csv LAUNCH genflist.vbs|123 WHILE (CHKFILE('FileList.csv')) = 0 THEN ENDWHILE GATEWAY IMPORT CSV filelist.csv REPLACE tflist OPTION SPECIFICATION_FILE_NAME FileList.rgw SELECT fname INTO vgoodfname FROM tflist WHERE rval IN (SELECT MAX rval FROM tflist) WRITE .vgoodfname WRITE 'Done!' DROP TABLE tflist NOCHECK CLEAR VAR vgoodfname RETURN Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) On 2/9/2011 10:16 AM, karentel...@cs.com wrote: Using 7.6, but not sure version matters in this. I have a directory that will contains files that are called by the part#, followed by the letter "R", and then some number that indicates a revision.  I want to launch that file.  IE: for part# 12345, there would be a file called:        12345R3.doc Of course, I have no idea what the number after the "R" is.   I can think of 2 "low tech" ways of doing this, but wondering if there's better ideas.  (1)  I can do a DIR .../B to a file, load into a table, but then they're seeing the DOS box. (2)  I can do a while loop to some finite ending number. Any quicker/better ideas? Karen