Search code examples
vbaexcelexacttargetfile-location

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

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.

Solution

  • 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: