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
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
- 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