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    : https://nolongerset.com/finding-the-temporary-folder-with-vba/
' Purpose   : Returns something like:
'               C:\Users\Mike\AppData\Local\Temp\
' Notes     - Use PathJoin() function to simplify backslash handling
'               https://nolongerset.com/joining-paths-in-vba/
'---------------------------------------------------------------------
'
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