Code Library

GetTempPath: Using the Windows API for Maximum Performance

If you are looking to maximize performance in VBA, using the Windows API will almost always be your best bet.

Mike Wolfe

Aug 8, 2022 • 4 min read

I've already written an article about how to use the FileSystemObject to retrieve the temporary folder path:

Creating an instance of the FileSystemObject is a relatively expensive process–at least compared to calling a Windows API function. Let's look at a faster alternative.*

* I should note that, unless it's being called inside a loop, creating an instance of the FileSystemObject is not something your actual end users are likely to notice. We're talking milliseconds here...

The Win API Function: GetTempPathA

We will be using the GetTempPathA Windows API function.

The function returns the first path found from this list:

TMP environment variable
TEMP environment variable
USERPROFILE environment variable
The Windows directory

There are some important caveats:

Note that the function does not verify that the path exists, nor does it test to see if the current process has any kind of access rights to the path. The GetTempPath function returns the properly formatted string that specifies the fully qualified path based on the environment variable search order as previously specified. The application should verify the existence of the path and adequate access rights to the path prior to any use for file I/O operations.

And one note that we can safely ignore:

Apps should call GetTempPath2 instead of GetTempPath.

Why are we ignoring this? Because for our purposes GetTempPathA and GetTempPath2A will return identical results. Here's the relevant note from GetTempPath2A's documentation:

For non-SYSTEM processes, GetTempPath2 will behave the same as GetTempPath.

Hopefully this goes without saying, but you should not be running your Microsoft Access databases with SYSTEM-level permissions.

The Code

Without further ado, here is the code:

'Place the following two lines in the module's header:
Private Declare PtrSafe Function apiGetTempPath Lib "kernel32" Alias "GetTempPathA" _
    (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

' ----------------------------------------------------------------
' Procedure : GetTempPath
' Date      : 8/8/2022
' Author    : Mike
' Source    : https://nolongerset.com/gettemppath/
' Purpose   : Uses the Windows API to return the path to the user's temp folder.
' Notes     : Returns a path including the trailing backslash.
' Usage     : Debug.Print GetTempPath
'             C:\Users\Mike\AppData\Local\Temp\
' ----------------------------------------------------------------
Function GetTempPath() As String
    'Maximum Windows path length is 260 characters
    Const MAX_PATH As Long = 260
    
    'Create a string and fill it with 260 spaces
    Dim Buffer As String
    Buffer = Space(MAX_PATH)

    'Call the API GetTempPathA function;
    '   this function returns the first path found from this list:
    '   1. TMP environment variable
    '   2. TEMP environment variable
    '   3. USERPROFILE environment variable
    '   4. Windows directory
    '
    '   It places the above value (with trailing slash) in the Buffer [out] argument
    '       and returns the length of the path which we use to trim the extra spaces.
    Dim PathLength As Long
    PathLength = apiGetTempPath(MAX_PATH, Buffer)
    
    'Trim the excess spaces from the buffer based on the returned path length
    GetTempPath = Left(Buffer, PathLength)
End Function

Supporting Functions

I strongly recommend using my PathJoin() function as part of any solution that uses GetTempPath():

If you are concerned that the folder may not exist, you can also use my VerifyFolder() function, which recursively creates any folders missing from a given path: