Get the Windows Download folder's path

Question

I have some Excel VBA code that requires knowing the Downloads folder path. How could I do it?

Since you can move around the Downloads folder (and also Documents and most of those folders, via the folder properties), the environmental variables like %USERPROFILE% are useless to construct a path like %USERPROFILE%\Downloads, and WScript.Shell.SpecialFolders doesn't list the Downloads folder.

I guess it has to be done reading the registry, but I'm clueless about that.

Thanks!

Attributive

"C:\Users\" & Environ("UserName") & "\Downloads"? – Ergotism

The point of the whole question is that the Downloads folder, like the Documents folder and many others, can be anywhere. While the User Profile will always be in c:\Users\simoco, the Documents folder can easily be moved to d:\stuff. – Attributive

@DmitryPavliv Or simpler: Environ("USERPROFILE") & "\Downloads" - but that doesn't answer the question in the sense that a user may rename his Downloads folder. – Nonu

Answer 1

None of the above registry or other solutions are necessary. The following will do it, even if My Documents is redirected to OneDrive:

Function GetMyDocuments() As String
Dim oWSHShell As Object

Set oWSHShell = CreateObject("WScript.Shell")
GetMyDocuments = oWSHShell.SpecialFolders("MyDocuments")
Set oWSHShell = Nothing
End Function

Or to get the Desktop folder:

Function GetDesktop() As String
Dim oWSHShell As Object

Set oWSHShell = CreateObject("WScript.Shell")
GetDesktop = oWSHShell.SpecialFolders("Desktop")
Set oWSHShell = Nothing
End Function

Tried, tested, and works.

Inbeing

Answer 2

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 find the registry key with the folder path.

' Downloads Folder Registry Key
Private 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 = pathTmp
End 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 registry
End Function

Sinapism

Nice! How would I do the same for Mac? – Dalessandro

@Dalessandro for a How To on Mac, check out this page from Ron de Bruin. – Sinapism

Answer 3

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 String
Dim 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.

Attributive

This returns "%USERPROFILE%\Downloads", which VBA can't make sense of. – Looby

@JonPeltier I don't even remember this issue but I unaccepted the answer on account of your comment. – Attributive

Answer 4

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 Explicit

Private Type GuidType
  data1 As Long
  data2 As Long
  data3 As Long
  data4 As Long
End Type

Declare Function SHGetKnownFolderPath Lib "shell32.dll" (ByRef guid As GuidType, ByVal flags As Long, ByVal token As Long, ByRef hPath As Long) As Long
Declare Function lstrlenW Lib "kernel32.dll" (ByVal hString As Long) As Long
Declare 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" folder
Function 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 = &H7B465E92
Dim result As Long
Dim hPath As Long
Dim converted As String
Dim 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 If
End Function

Weft

Answer 5

To use less code as possible you can Just run this PowerShell one-liner in VBA:

$downloadsFolder = (New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path

For how to run the .ps1 see here

You can also embed the one liner (But that's a new topic).

Saks

Answer 6

Sub GetDownloadedFolderFiles()
'
' Keep it simple - Paul Seré
'
Dim fso  As New FileSystemObject
Dim flds As Folders
Dim fls  As Files
Dim f    As File

'Downloads folder for the actual user!

Set fls = fso.GetFolder("C:\Users\User\Downloads").Files 

For Each f In fls
    Debug.Print f.Name
Next

End Sub

Slip

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Heimdall

Using the "FileSystemObject" is a much easier option than calling the more cumbersome APIs method. The "C: \ Users \ User \ Downloads" parameter in the GetFolder indicates the Downloads folder for the current user. – Wither

@Dalessandro you may want to post a separate question. The OP's question is specifically about Windows. – Sinapism

This answer is wrong because the location and name of the user's downloads folder can be anything. The user could have moved it, the user could have renamed it. It could exist on a completely different drive. The only correct way to find the folder location is through the SHGetKnownFolderPath and related APIs. – Winch

Answer 7

Why not read the Downloads folder from the registry with the correct GUID and mix the result with the user profile path ?

Function RegKeyRead(i_RegKey As String) As String
    
    Dim 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

Public Function Replace(strExpression As Variant, strSearch As String, StrReplace As String) As String

    Dim lngStart As Long
    
    If IsNull(strExpression) Then Exit Function
    
    lngStart = 1
    While InStr(lngStart, strExpression, strSearch) <> 0
        lngStart = InStr(lngStart, strExpression, strSearch)
        strExpression = Left(strExpression, lngStart - 1) & StrReplace & Mid(strExpression, lngStart + Len(strSearch))
        lngStart = lngStart + Len(StrReplace)
    Wend

    Replace = strExpression
    
End Function

Function GetDownloadedFolderPath() As String

    GetDownloadedFolderPath = RegKeyRead("HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\{374DE290-123F-4565-9164-39C4925E467B}")
    GetDownloadedFolderPath = Replace(GetDownloadedFolderPath, "%USERPROFILE%", Environ$("USERPROFILE"))

End Function

Echoism