VBA – Find Temporary Folder Path

To save any intermediate data files, instead of creating a folder, You can use system default temporary folders.

This way, there is less coding effort required to manage the new folders. But, to do that, You need to first find the folder path.

There may be multiple ways to do this. We are presenting 2 simple methods.

Find Temp Folder path in VBA

The 2 techniques used here are:

  1. Environment variables.
  2. File System Objects, Get special folders option

Lets see the code now.

Function findTempFolder() As String
    Dim sTempPath As String
    sTempPath = ""
    sTempPath = VBA.Environ("temp")
    Debug.Print sTempPath
    'Early Binding
    'Add reference to Microsoft Scripting Runtime Library from Tools
    'Dim eObjFileSystem As Scripting.FileSystemObject
    'Late Biding
    Dim objFileSystem
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    '0 - WindowsFolder
    '1 - SystemFolder
    '2 - TemporaryFolder
    Debug.Print objFileSystem.GetSpecialFolder(0)
    Debug.Print objFileSystem.GetSpecialFolder(1)
    Debug.Print objFileSystem.GetSpecialFolder(2)
    If sTempPath = "" Then sTempPath = objFileSystem.GetSpecialFolder(2)
    findTempFolder = sTempPath
End Function

Using this code, You also find Windows & System folder paths. Just change the parameter to GetSpecialFolder function as below

  • 0 – Windows Folder – Folder path of Windows installation
  • 1 – System Folder – Path to System32 folder
  • 2 – Temporary Folder – Temp Folder Path

Hope one of these 2 methods workout. If there are any more methods available, please leave a comment.

External Reference: Discussion on finding the windows default temporary path