Get the Windows Download folder's path

Simple Solution - usually works

This is from a comment by @assylias. As others have mentioned it will provide the wrong folder path if the user has changed the default "Downloads" location - but it's simple.

Function GetDownloadsPath() As String    GetDownloadsPath = Environ$("USERPROFILE") & "\Downloads"End Function

Best Solution

The posted answer was returning "%USERPROFILE%\Downloads". I didn't know what to do with it so I created the function below. This turns it into a function and returns the actual path. Call it like this: Debug.Print GetCurrentUserDownloadsPath or Debug.Print GetCurrentUserDownloadsPath. Thanks to @s_a for showing how to read a registry key and finding the registry key with the folder path.

' Downloads Folder Registry KeyPrivate Const GUID_WIN_DOWNLOADS_FOLDER As String = "{374DE290-123F-4565-9164-39C4925E467B}"Private Const KEY_PATH As String = "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\"'Public Function GetCurrentUserDownloadsPath()    Dim pathTmp As String    On Error Resume Next    pathTmp = RegKeyRead(KEY_PATH & GUID_WIN_DOWNLOADS_FOLDER)    pathTmp = Replace$(pathTmp, "%USERPROFILE%", Environ$("USERPROFILE"))    On Error GoTo 0    GetCurrentUserDownloadsPath = pathTmpEnd Function'Private Function RegKeyRead(registryKey As String) As String' Returns the value of a windows registry key.    Dim winScriptShell As Object    On Error Resume Next    Set winScriptShell = VBA.CreateObject("WScript.Shell")  ' access Windows scripting    RegKeyRead = winScriptShell.RegRead(registryKey)    ' read key from registryEnd Function

Found the answer google a little more...

The way to read the registry is, as per http://vba-corner.livejournal.com/3054.html:

'reads the value for the registry key i_RegKey'if the key cannot be found, the return value is ""Function RegKeyRead(i_RegKey As String) As StringDim myWS As Object  On Error Resume Next  'access Windows scripting  Set myWS = CreateObject("WScript.Shell")  'read key from registry  RegKeyRead = myWS.RegRead(i_RegKey)End Function

And the GUID for the Downloads folder, as per MSDN's http://msdn.microsoft.com/en-us/library/windows/desktop/dd378457(v=vs.85).aspx:

{374DE290-123F-4565-9164-39C4925E467B}

Thus RegKeyRead("HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\{374DE290-123F-4565-9164-39C4925E467B}") yields the current user's Downloads folder path.

The supported way to read such paths is to use the SHGetKnownFolderPath function.

I wrote this VBA code to do that. It has been tested in Excel 2000.

It won't work in any 64-bit version of Office. I don't know if its Unicode shenanigans will work in versions of Office more recent than 2000. It's not pretty.

Option ExplicitPrivate Type GuidType  data1 As Long  data2 As Long  data3 As Long  data4 As LongEnd TypeDeclare Function SHGetKnownFolderPath Lib "shell32.dll" (ByRef guid As GuidType, ByVal flags As Long, ByVal token As Long, ByRef hPath As Long) As LongDeclare Function lstrlenW Lib "kernel32.dll" (ByVal hString As Long) As LongDeclare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMemory As Long)Declare Sub RtlMoveMemory Lib "ntdll.dll" (ByVal dest As String, ByVal source As Long, ByVal count As Long)'Read the location of the user's "Downloads" folderFunction DownloadsFolder() As String' {374DE290-123F-4565-9164-39C4925E467B}Dim FOLDERID_Downloads As GuidType    FOLDERID_Downloads.data1 = &H374DE290    FOLDERID_Downloads.data2 = &H4565123F    FOLDERID_Downloads.data3 = &HC4396491    FOLDERID_Downloads.data4 = &H7B465E92Dim result As LongDim hPath As LongDim converted As StringDim length As Long    'A buffer for the string    converted = String$(260, "*")    'Convert it to UNICODE    converted = StrConv(converted, vbUnicode)    'Get the path    result = SHGetKnownFolderPath(FOLDERID_Downloads, 0, 0, hPath)    If result = 0 Then        'Get its length        length = lstrlenW(hPath)        'Copy the allocated string over the VB string        RtlMoveMemory converted, hPath, (length + 1) * 2        'Truncate it        converted = Mid$(converted, 1, length * 2)        'Convert it to ANSI        converted = StrConv(converted, vbFromUnicode)        'Free the memory        CoTaskMemFree hPath        'Return the value        DownloadsFolder = converted    Else        Error 1    End IfEnd Function