Get temporary folder path in VBA

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]AppDataLocalTemp”, 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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: