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
After all, you can just use
TempPath to get the temporary path and work with it! You may test it with the following codes.