1

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.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Daniel Roy
  • 105
  • 8

3 Answers3

2

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:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • it is in that location... just the user changes. So for me it is. C:\Users\095573\Documents the string of numbers needs to be the migratory variable. – Daniel Roy Aug 11 '18 at 21:26
  • @DanielRoy - Different versions of Windows are in different locations too, as I recall. There are many reasons it could be changed. My edited answer covers all possibilities I can think of.. – ashleedawg Aug 11 '18 at 21:31
  • Using the function method as described where would i place that? Nested in the function or or on a workbook_open function? – Daniel Roy Aug 11 '18 at 21:37
  • This is a 100% correct and definitely the best solution as the personal folder of a user to use as preferred storage location for documents can be any folder. Any user can very easily change the preferred folder for documents as Microsoft explains in the support article [Configuration of the My Documents folder](https://support.microsoft.com/en-us/topic/configuration-of-the-my-documents-folder-dfd9a90d-8f80-18d6-e7cc-f1566fc3b10b). – Mofi Apr 21 '21 at 07:09
0

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

strRootPath = "C:\Users\" + Environ("Username") + "\Documents"
ifo20
  • 738
  • 8
  • 20
0

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
Daniel Roy
  • 105
  • 8