Author: 1997ismail.hosen@gmail.com

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. Other Available Folder

Excel VBA Programming – The Complete Guide

Excel

Excel VBA & Scripts

Formulas

Power Query

Python

Outlook + Excel VBA

Others

Free Guide

The ONE Critical Excel Skill You Need

Author: 1997ismail.hosen@gmail.com

How to Get Downloads Folder Path from Registry Using VBA Win API

GitHub merge conflict for binary (excel) file

Round Time to Nearest N Minutes

List.Generate in Power Query – A Complete Guide With Examples

Advanced uses of the Web.Contents for data retrieving from API (Power Query)

Excel VBA Programming – The Complete Guide

Free Guide

The ONE Critical Excel Skill You Need

Stay Ahead with Weekly Insights!

Dive into Excel, Al and other essential tech news:

carefully crafted for the modern professional.