Incremental File Names

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

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)
    Loop

    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.