Getting the Temp Folder in VBA

tl;dr: Just use Environ("TMP").

There are several ways you can get the temporary folder in VBA:

• Environ("TMP"): use the Environ() VBA function to return the value of the user's TMP environment variable
• Environ("TEMP"): return the value of the user's TEMP environment variable (don't ask me, ask Raymond Chen)
• FSO.GetSpecialFolder(2): use the File System Object's GetSpecialFolder function
• TempPath(): my custom TempPath() function (which uses FSO.GetSpecialFolder())
• GetTempPath(): my custom GetTempPath() function (which uses API function GetTempPathA)

GetTempPath() API

Internally, the GetTempPath API function searches for temporary folder paths using the following priority:

1. The path specified by the TMP environment variable.
2. The path specified by the TEMP environment variable.
3. The path specified by the USERPROFILE environment variable.
4. The Windows directory.

FSO.GetSpecialFolder(2)

The folderspec argument of GetSpecialFolder can have one of three values.  

Value 2 represents the TemporaryFolder:

The Temp folder is used to store temporary files. Its path is found in the TMP environment variable.

My Advice

This is one of those topics where I've changed my opinion after learning new information.  

For a long time, I resisted using the Environ() function because it seemed too easy.  And so I created not one–but two (!)–custom functions to do what Environ() does for free.

So, now, I'll repeat my advice from above:

Just use Environ("TMP").

• That's what FSO.GetSpecialFolder(2) does.
• That's the first search priority of the GetTempPathA() Windows API function.
• It's very easy to do in VBA.

You gain a tiny bit of additional reliability with the API call in the exceedingly rare instance that the user's TMP environment variable has been deleted.  I no longer think that tradeoff makes sense.

One Big Caveat – The Trailing Backslash

There is no consistency among the many ways to retrieve a temp folder of whether the value returned includes a trailing backslash.  I suggest you use some simple code to add–or remove–the trailing backslash as needed.

In the code below, I use foTemp as the variable name that holds the temporary folder path.

Code to Ensure a Trailing Backslash

If Right(foTemp, 1) <> "\" Then foTemp = foTemp & "\"

Code to Ensure NO Trailing Backslash

If Right(foTemp, 1) = "\" Then foTemp = Left(foTemp, Len(foTemp)-1)

PathJoin()

Alternatively, you can just use my PathJoin() function and not have to worry about backslashes at all.