You've successfully subscribed to Nicholas Workshop
Great! Next, complete checkout for full access to Nicholas Workshop
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

Get Temporary Folder Path In VBA

Nicholas Wong
Nicholas Wong

It is normal to create temporary files and folders to facilitate the process in programs. Apart from doing it in your working directory, why don’t we make use of the default temporary folder built in the Windows system?

In the Windows working system, we know that the temporary folder can be opened by typing %temp% in run on the start menu. The location should be C:\Users\[Username]\AppData\Local\Temp, where [Username] is your user account name.

In VB, there are APIs which helps you to gather this information. And firstly, you have to add this declaration.

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long  

Then you might copy the following function which help you to work out the path.

Private Function TempPath() As String
    Const MaxPathLen = 256 ' Max length of the path, just as big as possible

    Dim FolderName As String ' Name of the folder
    Dim ReturnVar As Long ' Return Value
    FolderName = String(MaxPathLen, 0)
    ReturnVar = GetTempPath(MaxPathLen, FolderName)
    If ReturnVar <> 0 Then
        TempPath = Left(FolderName, InStr(FolderName, Chr(0)) - 1)
        TempPath = vbNullString
    End If
End Function

After all, you can just use TempPath to get the temporary path and work with it! You may test it with the following codes.

Sub TempTester()  
    MsgBox TempPath  
    Debug.Print TempPath  
End Sub  

Nicholas Wong

Fullstack software engineer with strong background in computer science and extensive experience in software engineering and architecture. Studied in NYU, worked in Yahoo, Rakuten and Manulife.