Hello Friends,

In this article I am going to explain you how to get the path of Special Folders in Windows Operating System. Using VBA we can get path of Special folders path like, Desktop, My Documents, Library etc. Few important special folders are listed below. There are many more special folders available but I am putting code for only 8 folders. At the end of this article you can download an Excel Workbook with code to play around with the code.

[checklist icon=”” iconcolor=”” circle=”” circlecolor=”” size=”18px” class=”” id=””][li_item icon=”fa-book”]How to get path of My Documents in Excel VBA[/li_item][li_item icon=”fa-desktop”]How to get path of Desktop in Excel VBA[/li_item][li_item icon=”fa-laptop”]3. How to get path of All User Desktop in Excel VBA[/li_item][li_item icon=”fa-file-text-o”]How to get path of Recent Documents in Excel VBA[/li_item][li_item icon=”fa-folder-open-o”]How to get the path of Favorites folder in Excel VBA[/li_item][li_item icon=”fa-folder-open”]How to get the path of Programs Folder in Excel VBA.[/li_item][li_item icon=”fa-windows”]How to get the path of Start Menu Folder in Excel VBA
[/li_item][li_item icon=”fa-share-square-o”]How to get the path of Send To Folder in Excel VBA[/li_item][/checklist]

There are three ways of finding the above path in Excel VBA.

1. Using WScript.Shell

2. Using Windows shfolder.dll

3. Using Excel VBA Function Environ$

1. How to get path of Special folders in Windows using Excel VBA

Sub GetSpecialFolderPath()
Dim objSFolders As Object
Set objSFolders = CreateObject("WScript.Shell").SpecialFolders
Sheets("Sheet1").Activate
With Sheets("Sheet1")
.Range("B2").Value = "My Document Path is:-         " & objSFolders("mydocuments")
.Range("B3").Value = "Desktop Path is:-             " & objSFolders("desktop")
.Range("B4").Value = "All User Desktop Path is:-    " & objSFolders("allusersdesktop")
.Range("B5").Value = "Recent Documents Path is:-    " & objSFolders("recent")
.Range("B6").Value = "Favorites Document Path is:-  " & objSFolders("favorites")
.Range("B7").Value = "Programs Path is:-            " & objSFolders("programs")
.Range("B8").Value = "Start Menu Path is:-          " & objSFolders("StartMenu")
.Range("B9").Value = "Send To Path is:-             " & objSFolders("SendTo")
End With
End Sub

2. Get Special Folder Path using shfolder.dll

About shfolder.dll:The shfolder.dll module is the DLL for shell folder services. The functions of the shfolder.dll include displaying of Windows special folders such as Desktop, My Documents, Programs etc. If this DLL is missing or disabled then accessing these folders is not possible.
Below is the Function which returns the Special Folder Path:

'***********************************
' Function to get the special
' folder path using WScript.Shell
'***********************************

Private Declare Function GetFolderPath Lib "shfolder.dll" _
    Alias "SHGetFolderPathA" _
    (ByVal hwndOwner As Long, _
    ByVal nFolder As Long, _
    ByVal hToken As Long, _
    ByVal dwReserved As Long, _
    ByVal lpszPath As String) As Long

Now we can call the above function to get the special folder path by passing the above mentioned parameters in the function. For Different folders all you need to change is hwndOwner and nFolder values while calling.

'**********************************
' Function to get the Folder Path
' using shfolder.dll
'**********************************
Function GetSFolderPath()
    Dim sBuffer As String
    sBuffer = Space$(260)
    'To get the My Documents Path
    If GetFolderPath(&H5, &H5, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D2").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Desktop Path
    If GetFolderPath(&H10, &H10, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D3").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the All User Desktop Path
    If GetFolderPath(&H19, &H19, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D4").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If     
    ' To get the Recent Document Path
    If GetFolderPath(&H8, &H8, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D5").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Favorites Path
    If GetFolderPath(&H6, &H6, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D6").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Program Path
    If GetFolderPath(&H2, &H2, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D7").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Start Menu Path
    If GetFolderPath(&HB, &HB, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D8").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Send To Path
    If GetFolderPath(&H9, &H9, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D9").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
End Function

3. Using Excel VBA Function Environ$()

Note: You can also try getting folder paths using Environ$() VBA function. But this is not reliable. You can try getting the Temp Folder path as below:

Function GetSpecialPath()
    MsgBox Environ$("temp")
    MsgBox Environ$("userprofile")
End Function

I have used Environ$() function to get the Temp Folder Path in Windows in most of the send email articles.

You can download the file to play around !!