how can a target the current user's documents folder?

Question

So we have a system that requires users to log in and it has there own roaming profile. So in this string of code how can i target the current users document folder? (FYI excel 2010)

'WORKAROUND:
Dim PID As Double
Dim strRootPath As String

Const strExpExe = "explorer.exe"
Const strArg = " "    '" /e,/root, "

'this is where i need to figure out how to target current user's documents
'// Change rootpath here
strRootPath = "C:\Data Files"

PID = Shell(strExpExe & strArg & strRootPath, 3)

the rest of the function does great... it opens file explorer i just cant figure the syntax for telling it to look for the current user.

Answer 1

Probably the best way would be with a function like this:

Function docsFolder() As String
    docsFolder = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
End Function

There are other ways too but this one will work on any version of Windows and with user customizations.

For example, in my case, I have my documents folder on a mapped X: drive, so simply stuffing my username into a C:\ path would not work.

---

More Information:

• Stack Overflow : Language independent way to get “My Documents” folder in VBA
• Microsoft Technet : What is the path to My Documents?
• MSDN : Wshell: SpecialFolders Property

Answer 2

I'm not sure how flexible you want this to be but you could try the following

strRootPath = "C:\Users\" + Environ("Username") + "\Documents"

Answer 3

Got it! thanks! for anyone that might care... The final string that made her run!

Function docsFolder() As String
docsFolder = 

CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
End Function




Private Sub test()

Dim PID As Double
Dim strRootPath As String

Const strExpExe = "explorer.exe"
Const strArg = " "    '" /e,/root, "


'// Change rootpath here
strRootPath = "C:\Users\" + Environ("Username") + "\Documents"

PID = Shell(strExpExe & strArg & strRootPath, 3)

End Sub