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

Working with Files, Folders, and Drives

I just ran across this article from Microsoft. It covers off a whole list of techniques for accessing files and folders. It overviews the VBA functions available and discusses a couple of libararies that you can access for more capabilities. It doesn''t go into detail on all the functions and methods, but provides links to further information on many of the functions. Some sample code is included.

Quote:
If you want to work with files or the file system, you have several options available—and the best choice depends on what specifically you want to accomplish. The available options include using VBA functions, the Microsoft Scripting Runtime Object Library, the Office FileSearch object, and file system-related Windows API functions. The only things I will not cover are the Windows API functions.

Programming the FileSystemObject

The FileSystemObject is provided to interact with files and folders from VBA and from Windows Scripts. This reference documentation from Microsoft provides details on some of its functions.