Getting the Temp Folder in VBA
There are many ways to get the temporary folder in VBA. But if you look behind the curtain, there's really only one...
tl;dr: Just use
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
Environ("TEMP"): return the value of the user's
TEMPenvironment 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
GetTempPath(): my custom GetTempPath() function (which uses API function GetTempPathA)
Internally, the GetTempPath API function searches for temporary folder paths using the following priority:
- The path specified by the TMP environment variable.
- The path specified by the TEMP environment variable.
- The path specified by the USERPROFILE environment variable.
- The Windows directory.
The folderspec argument of GetSpecialFolder can have one of three values.
2 represents the TemporaryFolder:
The Temp folder is used to store temporary files. Its path is found in the TMP environment variable.
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:
- That's what
- 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)
Alternatively, you can just use my
PathJoin() function and not have to worry about backslashes at all.