Get the Windows Download folder's path
excel, vba, windows, windows-shell, wsh
Solution
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.
Problem
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!