Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

SpecialFolders and Windows Temp folder

How do I get the path of a special folder in Windows and open the folder?, below are two way to get the path of a special folder so you can use it in your code.

Sub GetSpecialFolder()
'Special folders are : AllUsersDesktop, AllUsersStartMenu
'AllUsersPrograms, AllUsersStartup, Desktop, Favorites
'Fonts, MyDocuments, NetHood, PrintHood, Programs, Recent
'SendTo, StartMenu, Startup, Templates
'Get Favorites folder and open it
    Dim WshShell As Object
    Dim SpecialPath As String

    Set WshShell = CreateObject("WScript.Shell")
    SpecialPath = WshShell.SpecialFolders("Favorites")
    MsgBox SpecialPath
    'Open folder in Explorer
    Shell "explorer.exe " & SpecialPath, vbNormalFocus
End Sub

Sub VBA_GetSpecialFolder_functions()
'Here are a few VBA path functions
    MsgBox Application.Path
    MsgBox Application.DefaultFilePath
    MsgBox Application.TemplatesPath
    MsgBox Application.StartupPath
    MsgBox Application.UserLibraryPath
    MsgBox Application.LibraryPath
End Sub


Windows Temp folder

Without code you can do this to open the temp folder

Enter %temp%

Or use one of the two code examples

Sub GetTempFolder_1()
    MsgBox Environ("Temp")
    'Open folder in Explorer
    Shell "explorer.exe " & Environ("Temp"), vbNormalFocus
End Sub

Sub GetTempFolder_2()
    Dim FSO As Object, TmpFolder As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    Set TmpFolder = FSO.GetSpecialFolder(2)
    MsgBox TmpFolder
    'Open folder in Explorer
    Shell "explorer.exe " & TmpFolder, vbNormalFocus
End Sub

0 = The Windows folder contains files installed by the Windows operating sys
1 = The System folder contains libraries, fonts, and device drivers
2 = The Temp folder is used to store temporary files. Its path is found in the TMP environment variable.


Clear the Temp folder

It is smart to delete all files and folders in your temp folder at least once a month to avoid problems.
Important: Do this always after you reboot your system.

Manual you can use this to open the folder and then delete all files and folders in the Temp folder.

Enter %temp%

It is possible that there are a few files that you can't delete but you can skip them and this is no problem.