If you are working with VBA and need to access files in the Downloads folder, you might wonder how to get the path of this folder programmatically. The Downloads folder is not a fixed location that can be easily retrieved by using a predefined constant or an environment variable. In fact, it can vary depending on the user settings and preferences.
One way to get the Downloads folder path is to use the Windows Registry. The Registry is a database that stores configuration settings and options for Windows and other applications. You can use VBA to read and write values from the Registry using some Windows API functions.
In this blog post, I will show you how to get the Downloads folder path from Registry using VBA. I will also explain some of the challenges and limitations of this approach.
Here i have added a sample function to get user download folder path using Win API and Registry.
Private Const HKEY_CURRENT_USER = &H80000001
Private Const KeyName As String = "SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders"
Private Const DOWNLOADS_FOLDER_GUID As String = "{374DE290-123F-4565-9164-39C4925E467B}"
Private Const MAX_DATA_BUFFER_SIZE As Long = 1024
Private Const REG_SZ As Long = 1
Private Const NO_ERROR As Long = 0
#If VBA7 Then
Private Declare PtrSafe Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" ( _
ByVal hKey As LongPtr, _
ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare PtrSafe Function RegQueryValueExStr Lib "advapi32" Alias "RegQueryValueExA" ( _
ByVal hKey As LongPtr, _
ByVal lpValueName As String, _
ByVal lpReserved As Long, _
ByRef lpType As Long, _
ByVal szData As String, _
ByRef lpcbData As Long) As Long
Private Declare PtrSafe Function RegCloseKey Lib "advapi32.dll" ( _
ByVal hKey As LongPtr) As Long
#Else
Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" ( _
ByVal hKey As Long, _
ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare Function RegQueryValueExStr Lib "advapi32" Alias "RegQueryValueExA" ( _
ByVal hKey As Long, _
ByVal lpValueName As String, _
ByVal lpReserved As Long, _
ByRef lpType As Long, _
ByVal szData As String, _
ByRef lpcbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" ( _
ByVal hKey As Long) As Long
#End If
Private Sub Test()
Debug.Print GetUserDownloadsFolderPath()
End Sub
Public Function GetUserDownloadsFolderPath() As String
Dim KeyHandle As Long
Dim ReturnValueOfDLL As Long
ReturnValueOfDLL = RegOpenKey(HKEY_CURRENT_USER, KeyName, KeyHandle)
If ReturnValueOfDLL <> NO_ERROR Then
RegCloseKey KeyHandle
Exit Function
End If
Dim Path As String
Path = String$(MAX_DATA_BUFFER_SIZE, vbNullChar)
Dim PathLength As Long
PathLength = MAX_DATA_BUFFER_SIZE
ReturnValueOfDLL = RegQueryValueExStr(hKey:=KeyHandle, lpValueName:=DOWNLOADS_FOLDER_GUID, lpReserved:=0&, _
lpType:=REG_SZ, szData:=Path, lpcbData:=PathLength)
If ReturnValueOfDLL = NO_ERROR Then GetUserDownloadsFolderPath = VBA.Left(Path, PathLength)
RegCloseKey KeyHandle
End Function
In the same way, you can retrieve other folder paths from registry.
