Use WShell to Retreive the Path to Windows Special Folders

Sometimes in your program you'll need to find the path of the user's MyDocuments folder, or to the Desktop, or to the Favorites folder. Because different versions of Windows will place these folders in different places, it's important to ask Windows for the location of the file, instead of hard coding it or figuring it out from the user name.

This post describes a method for using the Windows Script Host to retrieve these names. The Microsoft documentation is here: SpecialFolders Property.

Here's an example of how to build a VBA function that uses this method within MS Access to find the path to the user's MyDocuments folder.

Public Function GetDocumentsFolder() As String
Dim objWShell As Object

Set objWShell = CreateObject("WScript.Shell")

GetDocumentsFolder = objWShell.SpecialFolders("MyDocuments")
End Function

You can use the following constants instead of MyDocuments to get the path to other special folders:

AllUsersDesktop 
AllUsersStartMenu 
AllUsersPrograms 
AllUsersStartup 
Desktop 
Favorites 
Fonts 
MyDocuments 
NetHood 
PrintHood 
Programs 
Recent 
SendTo 
StartMenu 
Startup 
Templates 

Notably absent from the list above is the user's Temp folder. See the additional reading before-I've posted a method for getting that path previously here. There's also an post linking to an API method to get special folders here.