Getting a Temporary File Name with VBA

Yesterday, I wrote about the TempPath() function that I use to easily find the current user's temporary folder.  

If I'm being honest, though, I rarely use that function.  In most cases, the reason I need to find the temporary folder is because I am creating a temporary file.  And to do that, I use my TempFileName() function.

The Code: TempFileName()

'-----------------------------------------------------------------------------------
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/tempfilename/
' Purpose   : Returns an unused file name but does not create the file.  Path can be
'             passed with or without the trailing '\'.
' Requires  : TempPath() function:
'                 https://nolongerset.com/finding-the-temporary-folder-with-vba/
' Notes     - Supports up to 2,147,483,647 temp files with same prefix (2 ^ 31) - 1
'           - Performance will likely be terrible if the number of existing temp
'               files grows too high; remember to clean up your temp files!
'           - To improve performance, we create the FSO one time outside the loop
'           - Requires absolute path (helps avoid subtle bugs)
'-----------------------------------------------------------------------------------
'
Function TempFileName(Optional ByVal Path As String = "WINDOWS TEMP FOLDER", _
                      Optional Ext As String = "txt", _
                      Optional Prefix As String = "temp") As String

    If Path = "WINDOWS TEMP FOLDER" Then Path = TempPath
    If Right(Path, 1) <> "\" Then Path = Path & "\"
    If Not (Path Like "?:\*" Or Path Like "\\*") Then
        Err.Raise 52    '"Bad file name or number."
    ElseIf Not FolderExists(Path) Then
        Err.Raise 76    '"Path not found."
    End If

    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
        
    Dim TempFName As String
    TempFName = Path & Prefix & "." & Ext
    Do
        If Not oFSO.FileExists(TempFName) Then
            TempFileName = TempFName
            Exit Function
        End If
        Dim i As Long
        i = i + 1
        TempFName = Path & Prefix & "_" & Format(i, "000") & "." & Ext
    Loop
End Function

Some notes about the code

The function uses sensible defaults.  I usually call this code without passing any arguments.

The function will raise an error if the path does not exist.  Use the VerifyFolder() function to automatically create a path–with subfolders–if necesssary.

You can specify a file extension.  This is important because some Access import/export commands will fail if you use an unsupported file extension (even if the file's contents are properly formatted).

The function requires absolute paths.  If you really want to allow relative paths, you can just pull out the conditional check that raises error 52.  I have it in there to avoid subtle bugs.  For example, imagine you're using the value of a database field to create the folder variable.  One day that value is an empty string.  Suddenly, you're creating temporary files in whatever the current working directory happens to be.  I prefer to program defensively.

I include leading zeroes when formatting the file names of the first 99 entries.  This ensures reliable sorting for the first one thousand entries (001 - 999).  See example below.

Whither the GetTempFileName API Functions

The Windows API includes functions to retrieve temporary file names.  They're almost certainly faster than my TempFileName() function, but the practical difference is likely negligible.  They also have some limitations:

• the Path Name is required
• the file extension is always .TMP
• there is a maximum of 65,535 unique file names with the same prefix
• they probably use the same inefficient algorithm my function uses ;-)

"Due to the algorithm used to generate file names, GetTempFileName can perform poorly when creating a large number of files with the same prefix."

Example usage

The following function illustrates the usage of TempFileName().  I'm creating (and then deleting) a series of text files using my FileWrite() function.

Sub TestTempFileName()
    Const TestString As String = "@nolongerset"
    
    
    Dim i As Long, TempFiles As New Collection
    For i = 1 To Len(TestString)
        Dim Letter As String
        Letter = Mid(TestString, i, 1)
        
        Dim FName As String
        FName = TempFileName()
        TempFiles.Add FName
        
        FileWrite FName, Letter
        
        Debug.Print Letter, FName
    Next i
    
    Debug.Print "-----------------"
    
    'Clean up temp files
    Dim TempFName As Variant
    For Each TempFName In TempFiles
        Debug.Print "Removing "; TempFName
        Kill TempFName
    Next TempFName
End Sub

Image by FelixMittermeier from Pixabay