windowsvbaexcelwindows-shellwsh

Get the Windows Download folder's path

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!

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.