My Documents Default path in VBA - Irrespective of Current User

[raghuram.star]

I want to assign "My Documents" Path to a Variable, which should assign the Path in which ever system I run this Macro

Eg: Path of My Documents in my PC
C:\Documents and Settings\Rams\My Documents\

But If I hard code it in VBA, it will not work in other PCs with different User Names

Is there a way to assign default path of My Documents to an variable? So that I can run my macro from any where...!!

Any suggestions will be great help

Thanks in advance

 

[SydneyGeek]

Paste this code into a new module.

Option Explicit
     ' Declare for call to mpr.dll.
   Declare Function WNetGetUser Lib "mpr.dll" _
      Alias "WNetGetUserA" (ByVal lpName As String, _
      ByVal lpUserName As String, lpnLength As Long) As Long
   Const NoError = 0       'The Function call was successful

   Function GetUserName()
      ' Buffer size for the return string.
      Const lpnLength As Integer = 255
      ' Get return buffer space.
      Dim status As Integer
      ' For getting user information.
      Dim lpName, lpUserName As String
      ' Assign the buffer size constant to lpUserName.
      lpUserName = Space$(lpnLength + 1)
      ' Get the log-on name of the person using product.
      status = WNetGetUser(lpName, lpUserName, lpnLength)
      ' See whether error occurred.
      If status = NoError Then
         ' This line removes the null character. Strings in C are null-
         ' terminated. Strings in Visual Basic are not null-terminated.
         ' The null character must be removed from the C strings to be used
         ' cleanly in Visual Basic.
         lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
      Else
         ' An error occurred.
         MsgBox "Unable to get the name."
         End
      End If
      GetUserName = lpUserName
   End Function

Function MyDocs() As String
    Dim strStart As String
    Dim strEnd As String
    Dim strUser As String
    
    strUser = GetUserName()
    strStart = "C:\Documents and Settings\"
    strEnd = "\My Documents\"
    
    MyDocs = strStart & strUser & strEnd
End Function

To get the user's documents path do something like:

Dim strDocs As String

strDocs = MyDocs()

Denis

 

[patel45]

try this:
MsgBox Application.ThisWorkbook.Path

 

[jimrward]

Function mydocs () as string
Set WshShell = CreateObject("WScript.Shell")
Mydocs= WshShell.SpecialFolders("MyDocuments")
End function

 

[SydneyGeek]

Jim, that's much neater than my suggestion.
Thanks -- I didn't think of using Windows Scripting.

Denis

 

[jimrward]

Cheers, I also use something similar for desktop location, I know some people redirect MyDocuments, so let windows look after it, hence the short code

 

[raghuram.star]

@ SydneyGeek : Thank you for the coming up with a solution. that was nice

 

[raghuram.star]

@ jimrward :Thank you, that's pretty neat and straight forward..