[VBA] How to locate the Documents folder?!?

[simurq]

Good evening!

I'm using the following piece of code (found on the net) to determine the operating system. It works as required (determines OS) but my real challenge is to locate the Documents folder regardless of OS locale or name of Documents folder in another language. I searched the registry and OS environment variables but none of them points to a "stable" location which I can use in my project. Can you pls help to modify the code?

Thanks!

Option Explicit


Private Type OSVERSIONINFO
    dwOSVersionInfoSize As Long
    dwMajorVersion As Long
    dwMinorVersion As Long
    dwBuildNumber As Long
    dwPlatformId As Long
    szCSDVersion As String * 128
End Type


Declare PtrSafe Function GetVersionExA Lib "kernel32" (lpVersionInformation As OSVERSIONINFO) As Integer


Public Function getVersion() As String
    Dim osinfo As OSVERSIONINFO
    Dim retvalue As Integer


    osinfo.dwOSVersionInfoSize = 148
    osinfo.szCSDVersion = Space$(128)
    retvalue = GetVersionExA(osinfo)

    Select Case osinfo.dwMajorVersion + osinfo.dwMinorVersion / 10
        Case 5.1, 5.2               '<-- Check if Windows XP
            ChDir (Environ("USERPROFILE") & "\My Documents")
            'Do something else
        Case 6#, 6.1               '<-- Check if Windows Vista or 7
            ChDir (Environ("USERPROFILE") & "\Documents")
            'Do something else
        Case Else
            getVersion = "Other version"
    End Select
End Function

 

[Andrew Poulsom]

Does this work for you?

Sub MyDocs()
    MsgBox CreateObject("WScript.Shell").SpecialFolders("mydocuments")
End Sub

 

[simurq]

It works like a charm, thank you very much!!!

I applied your code to my needs as this:

ChDir (CreateObject("WScript.Shell").SpecialFolders("mydocuments"))

 

[simurq]

I assume that it will also work when there are multiple user accounts on the same rig, right?
I just tested it on my rig with two "Desktop" folders on two different accounts (Public and User) and it opens the one on a user account from which the code is executed.

Thanks!

 

[Andrew Poulsom]

Yes, it returns the path to the current user's Documents folder.