interrogate windows file names 1

[NXMold]

I have folders named \[job_number]\ that contain a series of work orders [job_number]_a.doc [job_number]_b.doc and so on. My database is going to create a new document in this folder, and I would like to have it named with the next consecutive letter.

Currently all is working, but new files are named [job_number]_new.doc since there is nothing in the datbase which would indicate what the next available letter is.

Is there a way (reasonably simple and direct, I hope) to gather a list of files in access so that I may discover the highest letter? Once mined, I would like to use a dlookup on this data to get the highest filename, and pick out the last letter using mid([field],6,1)... all that is easy. Just not sure how to grab the file names or where in access I would put them (temp table? using recordsets?).

 

[RuralGuy]

I'll bet there are some clues on how to do this in Allen Browne's code in this link.

http://allenbrowne.com/ser-59.html

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[Remou]

How about the FileSystemObject?

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set fl = fs.GetFolder("C:\Docs")
    
    For Each f In fl.Files
        ltr = Asc(LCase(Mid(f.Name, InStr(f.Name, ".") - 1, 1)))
        If ltr >= 97 And ltr <= 122 Then
            If IsNull(sltr) Or ltr > sltr Then
                sltr = ltr
            End If
        End If
    Next
    
    sltr = sltr + 1
    
    If sltr > 122 Then
        MsgBox "Run out of letters."
    Else
        MsgBox Chr(sltr)
    End If

 

[TheAceMan1]

How are ya NXMold . . .

If you hold the last assigned highest value in a [blue]custom db property[/blue], you'll always know and will [blue]save yourself loading and scanning files for the highest used![/blue]

Noteb properties are [blue]non-volatile[/blue] and stay with the Db, even if its closed!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[NXMold]

Remou, I like that example very much. I'll be using the filesystemobject already so it will integrate quite nicely. Interesting code, I would not have put it together so cleanly first time through (learned some new tricks). Thanks.

AceMan, this is integrating into a system that has been maintained manually for years and must pick up where people have left off, and also allow for them to keep creating files outside the database if they choose. Plus, I would have to track several thousand jobs.

I was not aware of custom db properties though, and will have to look into that to see if its something I could use elsewhere.