Get Special Folders Path in VBA

While writing small or big applciations in Excel, you might come across a need to get the path of Special folders in windows. As these paths vary depending on the user/environment they are being used, there is no fixed value to it. 

To satisfy this need, you can take help VBA Macros. Following is the code 

Sub GetSpecialFolderPath()

   

    Dim objSFolders As Object

    Set objSFolders = CreateObject("WScript.Shell").SpecialFolders

   

    'Make the result sheet visible if not

    Sheets("Sheet1").Activate

   

    'Get "My Documents" Path

    Sheets("Sheet1").Cells(2, 1).Value = "My Documents"

    Sheets("Sheet1").Cells(2, 2).Value = objSFolders("mydocuments")

   

    'Get "Desktop" Path

    Sheets("Sheet1").Cells(3, 1).Value = "Desktop"

    Sheets("Sheet1").Cells(3, 2).Value = objSFolders("desktop")

   

    'Get "All Users Desktop" Path

    Sheets("Sheet1").Cells(4, 1).Value = "All User Desktop"

    Sheets("Sheet1").Cells(4, 2).Value = objSFolders("allusersdesktop")

   

    'Get "Recent Documents" Path

    Sheets("Sheet1").Cells(5, 1).Value = "Recent Documents"

    Sheets("Sheet1").Cells(5, 2).Value = objSFolders("recent")

   

    'Get "Favorites Document" Path

    Sheets("Sheet1").Cells(6, 1).Value = "Favorites Document"

    Sheets("Sheet1").Cells(6, 2).Value = objSFolders("favorites")

   

    'Get "Programs" Path

    Sheets("Sheet1").Cells(7, 1).Value = "Programs"

    Sheets("Sheet1").Cells(7, 2).Value = objSFolders("programs")

   

    'Get "Start Menu" Path

    Sheets("Sheet1").Cells(8, 1).Value = "Start Menu"

    Sheets("Sheet1").Cells(8, 2).Value = objSFolders("StartMenu")

   

    'Get "Send To" Path

    Sheets("Sheet1").Cells(9, 1).Value = "Send To"

    Sheets("Sheet1").Cells(9, 2).Value = objSFolders("SendTo")

   

End Sub

You should see a result similar to below 

You can also download the sample code sheet here.