[Excel VBA] How to Reliably Get Desktop and Documents Folder Paths

You often encounter situations where you want to save a file created by a macro to the user’s Desktop or read a file from the Documents folder.

However, these folder paths vary depending on the username, such as C:\Users\John\Desktop. If you write the path directly into your VBA code (hardcoding), the macro will not work on other people’s computers.

In this article, I will explain how to use the WScript.Shell object to reliably get the path of special folders in the current PC environment, ensuring it works for any user.

The Completed VBA Code

Sub GetSpecialFolderPath()

    ' Declare variables
    Dim wsh As Object
    Dim desktopPath As String
    Dim documentsPath As String
    
    '--- 1. Create WScript.Shell object ---
    Set wsh = CreateObject("WScript.Shell")
    
    '--- 2. Get path of special folders using SpecialFolders property ---
    desktopPath = wsh.SpecialFolders("Desktop")
    documentsPath = wsh.SpecialFolders("MyDocuments")
    
    '--- 3. Display retrieved paths ---
    MsgBox "The paths on this PC are:" & vbCrLf & vbCrLf & _
           "Desktop: " & desktopPath & vbCrLf & _
           "Documents: " & documentsPath, vbInformation, "Special Folder Paths"
           
    '--- 4. Release object ---
    Set wsh = Nothing

End Sub

Explanation of Key Points

1. CreateObject(“WScript.Shell”)

This acts like a magic spell to control various Windows functions (Shell functions) from VBA. It creates the wsh object, which allows you to access OS information.

2. wsh.SpecialFolders(“Folder Name”)

This is the core part of retrieving the path. By passing a specific string indicating the desired folder to the .SpecialFolders property, it returns the full path.

Using this method ensures you always get the correct path, regardless of the PC environment or Windows language settings.

List of Main Special Folders

Here are the typical folder names you can specify as arguments for SpecialFolders:

Argument String	Corresponding Special Folder
Desktop	Desktop
MyDocuments	Documents
Favorites	Favorites
Startup	Startup
Recent	Recent Files
SendTo	Send To
Templates	Templates
Fonts	Fonts

[Application] Saving a File to the Desktop

By using this technique, you can reliably perform tasks like “saving a file to the current user’s Desktop.”

Sub SaveFileToDesktop()
    Dim wsh As Object
    Dim desktopPath As String
    
    Set wsh = CreateObject("WScript.Shell")
    desktopPath = wsh.SpecialFolders("Desktop")
    
    ' Combine desktop path and file name to create full path
    ThisWorkbook.SaveAs Filename:=desktopPath & "\SaveTestFromVBA.xlsx"
    
    MsgBox "File saved to Desktop."
    
    Set wsh = Nothing
End Sub

Summary

When getting paths for special folders that depend on the user environment, do not write the path directly in the code. Instead, use CreateObject("WScript.Shell").SpecialFolders("Folder Name").

This prevents the situation where the macro “only works on my PC” and allows you to create versatile tools that operate correctly for anyone.