I was recently doing some work in an old database trying to convert a very simple function which used Wscript.shell Specialfolders into a more versatile function that would accept a single input variable instead of a hardcoded value.

My original function was

Function m() As String
    Set WshShell = CreateObject("WScript.Shell")
    m = WshShell.SpecialFolders("Desktop")
End Function

Running it would return the path to the current user’s Desktop folder accurately.

C:\Users\MyUserName\Desktop

A very slight variation of the same function

Function mm() As String
    Set WshShell = CreateObject("WScript.Shell")
    sFldr = "Desktop"
    m = WshShell.SpecialFolders(sFldr)
End Function

would also return the path to the current user’s Desktop folder accurately.

C:\Users\MyUserName\Desktop

However, where things got weird was when I made another modification to use sFldr as an input variable and created the following function

Function n(sFldr As String) As String
    Set WshShell = CreateObject("WScript.Shell")
    n = WshShell.SpecialFolders(sFldr)
End Function

and ran it by calling by using the command n(“Desktop”) it would not return the current user’s Desktop folder, but rather the public desktop path???

C:\Users\Public\Desktop

So I started to do a little digging and came to find I was not the first person to encounter this issue. For instance, see:

Unexpected behavior of WScript.Shell SpecialFolders function

So I did a little more playing around with code and switched the input variable sFldr from String to Long

Function n(sFldr As Long) As String
    Set WSHShell = CreateObject("WScript.Shell")
    n = WSHShell.SpecialFolders(sFldr)
End Function

and performed a few iterations (1 to 16) and determined the following

1->ProgramData\Microsoft\Windows\Start Menu
2->ProgramData\Microsoft\Windows\Start Menu\Programs
3->ProgramData\Microsoft\Windows\Start Menu\Programs\Startup
4->User Desktop
5->User AppData\Roaming
6->User AppData\Roaming\Microsoft\Windows\Printer Shortcuts
7->User AppData\Roaming\Microsoft\Windows\Templates
8->Windows\Fonts
9->User AppData\Roaming\Microsoft\Windows\Network Shortcuts
10->User Desktop
11->User AppData\Roaming\Microsoft\Windows\Start Menu
12->User AppData\Roaming\Microsoft\Windows\SendTo
13->User AppData\Roaming\Microsoft\Windows\Recent
14->User AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup
15->User Favorites
16->User Documents
17->User AppData\Roaming\Microsoft\Windows\Start Menu\Programs

which is all odd to me. In the hardcoded version it expects a string value, but the last version takes a numeric value to get proper paths returned. The above (last function) has, by far, not been tested in any manner to ensure it is reliable, it is simply at this point an interesting observational fact.

What is a fact is that Wscript.shell Specialfolders does not appear to be reliable when the objWshSpecialFolders variable is not a hardcoded value! As such, I’d recommend using an alternate method to determine the paths of Windows Special folders like: Enumerating Special Folders which uses the CreateObject(“Shell.Application”).Application.Namespace() to get the values.

Update and Solution – 2016-09-28

Too stubborn for my own good, I couldn’t let this one go. So I kept playing around and testing… eventually I managed to get it to work. Below is the reusable function.

'---------------------------------------------------------------------------------------
' Procedure : GetWindowsSpecialFldrPath
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the full path for the specified Windows Special folder
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' vFldrName : The name of the folder to return the path of
'             Values can be: AllUsersDesktop, AllUsersStartMenu, AllUsersPrograms,
'                            AllUsersStartup, Desktop, Favorites, Fonts, MyDocuments,
'                            NetHood, PrintHood, Programs, Recent, SendTo, StartMenu,
'                            Startup, Templates
'
' Usage:
' ~~~~~~
' sPath = GetWindowsSpecialFldrPath("Desktop")
' sPath = GetWindowsSpecialFldrPath("MyDocuments")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' ********************************************************************************
******
' 1         2016-09-28              Initial Release
'---------------------------------------------------------------------------------------
Function GetWindowsSpecialFldrPath(vFldrName As Variant) As String
      '***Special Note: If vFldrName this is dimmed as a string it returns the wrong value ***
10        On Error GoTo Error_Handler
          Dim WshShell              As Object

20        If IsNull(vFldrName) = True Then GoTo Error_Handler_Exit
30        Set WshShell = CreateObject("WScript.Shell")
40        GetWindowsSpecialFldrPath = WshShell.SpecialFolders(vFldrName)

Error_Handler_Exit:
50        On Error Resume Next
60        If Not WshShell Is Nothing Then Set WshShell = Nothing
70        Exit Function

Error_Handler:
80        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: " & sModName & "\GetWindowsSpecialFldrPath" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occurred!"
90        Resume Error_Handler_Exit
End Function

The entire issue is the way the input variable, vFldrName, is dimmed. If it is dimmed as a string it acts weird. Dim it as a variant and it works as expected. Since the documentations that I found simply mentions “The name of the special folder.” and does not specify the type to use and the examples provided were .SpecialFolders(“Desktop”) one would think it is a string, but apparently not.

Alternate Solution Update 2016-09-29

User daolix on UtterAccess recently answered my post on this subject and provided an interesting answer/solution:

If you want to use a string variable so you have to force a ByVal call at the “Special Folders”.
Your function n is just to expand only to a clip.

Function n(sFldr As String) As String
    Set WshShell = CreateObject("WScript.Shell")
    n = WshShell.SpecialFolders((sFldr))
End Function

If you pass on a “ByRef” variant, a variable which does not correspond to the data type “Variant”, a variant is created at the interface of the function, which contains a pointer to the passed variable as a value. A true “ByRef” transfer does not take place here, this is only “simulated” with the help of the variant, and corresponds rather to a “ByVal” transfer.
If you pass a string variable to this function, the system must dereference twice to get the content, because a string itself is only a pointer. And here probably the function “SpecialFolders” failed. Error from Microsoft? So wanted? No idea.

I tested his answer and it does work.