Finding the Temporary Folder with VBA

Be a good steward of your users' file system. If you are creating temporary files, be sure to create them in the designated temporary folder.

Finding the Temporary Folder with VBA

If you need to create temporary files, you should create them in the Windows-designated temporary file folder.

This is easy to find using the FileSystemObject's GetSpecialFolder() method.  I could never remember the constant for the Temporary Folder–or the name of the method–so I wrote a simple function with an easy-to-remember name: TempPath().

The Code: TempPath()

' Author    : Mike Wolfe
' Source    :
' Purpose   : Returns something like:
'               C:\Users\Mike\AppData\Local\Temp\
' Notes     - Use PathJoin() function to simplify backslash handling
Function TempPath(Optional WithTrailingBackslash As Boolean = True) As String
    Static TempFolderPath As String

    If Len(TempFolderPath) = 0 Then
        Dim fs As Object
        Set fs = CreateObject("Scripting.FileSystemObject")
        Const TemporaryFolder = 2
        TempFolderPath = fs.GetSpecialFolder(TemporaryFolder)
    End If
    TempPath = TempFolderPath
    If WithTrailingBackslash Then TempPath = TempPath & "\"
End Function

Some notes about the code

Most built-in functions that return folders do so without including a trailing backslash.  Before I created and started using my PathJoin() function, I found that I almost always wanted to have the trailing backslash when I was working with temporary folders.  Thus, my TempPath function includes the trailing backslash by default.

Secondly, I cache the result of the GetSpecialFolder() call in a static variable.  This makes no noticeable difference on a single call to the function.  However, without this static variable, the constant creation and destruction of the FileSystemObject would likely impact performance if you called the function repeatedly within a loop.

Image by Free-Photos from Pixabay