'Serving the Microsoft® Access
Community since 1997'

Free Samples
About Us



Incremental File Names

A recent project for a client required a routine to export data to a text file format. The client wanted the first "batch" of each month to be named Ayyyymm.txt where the yyyymm was the current year and month. The second one in that month would be Byyyymm.txt, and so on. They had never gone past "K" in any one month and wanted to stay with the same naming style. The old manual system caused problems every 2 or 3 months because someone would give 2 batches in a row the same name.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

(article continues after sponsor spot)

Here is a simple version of the code I came up with. I'll leave it to you to add error checking and other embellishments.

Function NextFileName()

    Dim fileprefix As String*1
    Dim tempNextFileName As String
    Dim txtFilePath As String
    Dim strYYYYMM as String*6
    strYYYYMM = Format(Now(),"yyyymm")
    txtFilePath = "c:\exported_data\"
    fileprefix = "A"

    Do While True
        tempNextFileName = txtFilePath & fileprefix & _
                       strYYYYMM & ".txt"
        If Dir(tempNextFileName) = "" Then
            Exit Do
        End If
        fileprefix = Chr(Asc(fileprefix) + 1)

    NextFileName = tempNextFileName

End Function

The keys to the routine are the use of the Dir() function to check for an existing file, and the use of the Chr(Asc()) combination to increment the prefix character.  (not to mention the use of the Format function to created the yyyymm string)

Happy Coding

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.


© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy