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.

Read more »

Getting the path to windows special folders

Sometimes your VBA program needs the path to the users desktop, or to a temp folder where working files can be placed, or to the user's documents folder. There are several ways to do this.

There's some great API code here at The Access Web.

You can also do it using the Windows Scripting Host. That method, as well as another API method, is described here, in a previous blog post. That post talks specifically about getting the path to the temp folder. I can't see that choice within the Access Web code above. Perhaps no one approach offers all the special paths. In the previous blog post linked earlier in the paragraph I use GetSpecialFolder() to get the temp folder path. You can get a couple of other special folders with that method as well- see the Microsoft documentation for details.

Getting the path for the "Temp" folder

Way back in the days of DOS, and the early days of windows, you could usually put temporary files in C:\TEMP. Today Windows provides TEMP folders, but they won't be in the root of C:. In XP for instance the path will be C:\Documents and Settings\username\Local Settings\Temp. If you want to use that folder in your program, such as to save a copy of a report for sending via e-mail, you'll need to get the specific path name. In this post I'll show you two ways to ask Windows for the path for the TEMP folder.

Using an API

Application Programming Interfaces (APIs) provide a way for your program to call Windows functions that aren't otherwise available. There's an API called GetTempPath that does just what we want. You'll usually need to build a function around API calls to make them easier to use in your program. Paste this code into a standard module and you'll be able to use the function GetWindowsTempPath() to return the Temp folder name.

' delaration of API to get the special folder name for temp objects
' (in XP this is, for example, C:\Documents and Settings\user.name\Local Settings\Temp\)
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
    (ByVal nBufferlength As Long, ByVal lpBuffer As String) As Long


Const MAX_PATH = 260 ' maximum length of path to return- needed by API call to provide a memory block to put result in


' function GetTmpPath
' return windows special folder for temp objects
' path will not include a backslash on the end
' path will be expressed using short (8.3) names for folders
    
Public Function GetWindowsTempPath()
    Dim strFolder As String ' API result is placed into this string
    
    strFolder = String(MAX_PATH, 0)
    If GetTempPath(MAX_PATH, strFolder) <> 0 Then
        GetWindowsTempPath = Left(strFolder, InStr(strFolder, Chr(0)) - 1)
    Else
        GetWindowsTempPath = vbNullString
    End If
    
    ' remove any trailing backslash
    If Right(GetWindowsTempPath, 1) = "\" Then
        GetWindowsTempPath = Left(GetWindowsTempPath, Len(GetWindowsTempPath) - 1)
    End If
    
End Function

Using the FileSystemObject

The FileSystemObject is provided to interact with files and folders from VBA and from Windows Scripts. One of the many things the FileSystemObject can do for you is give you the paths to some of Windows special folders. Like the previous example, paste this code into a standard module and you'll be able to use the function GetWindowsTempPath() to return the Temp folder name. Because of the use of the FSO you'll need to go into Tools/References and add a reference to the Microsoft Scripting Runtime.

Public Function GetTempFolderName() As String
    ' use a FileSystemObject to returns the name of the user's temp folder
    ' in this case FSO is early bound- it's common to late bind with this library but
    ' this works well and provides compile time checking
    ' requires a reference to Microsoft Scripting Runtime
    ' path will not include a slash
    ' path will be expressed using short (8.3) names for folders
    
    Dim fso As Scripting.FileSystemObject ' an instance of the FileSystemObject
    
    Set fso = New FileSystemObject

    GetTempFolderName = fso.GetSpecialFolder(TemporaryFolder).Path
    
    Set fso = Nothing

End Function

Additional reading:

• Working with Files, Folders, and Drives: More VBA Tips and Tricks Includes a great overview of the FileSystemObject.
• Programming the FileSystemObject
• Windows API Microsoft reference for APIs.
• Getting The Path To Windows Special Folders