How To Find the directory of My Documents [VBA/VB6]

Find the directory of My Documents

In the file explorer the My Documents folder appears to be on the root or this is not the case, it is located in a sub directory C: \ Documents and Settings the bottom line is that the first sub directory takes username and change not only from one PC to another, but also on a multi PC users.
The following code allows for the My Documents directory of whatever the user connected.
Paste this code in a general module.

Code:

---

Option Explicit Private Type ****EMID cb As Long abID As Byte End Type Private Type ITEMIDLIST mkid As ****EMID End Type Private Const CSIDL_PERSONAL As Long = &H5 Private Declare Function SHGetSpecialFolderLocation Lib "shell32.dll" _ (ByVal hwndOwner As Long, ByVal nFolder As Long, _ pidl As ITEMIDLIST) As Long Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, ByVal pszPath As String) As Long Public Function Rep_Documents() As String Dim lRet As Long, IDL As ITEMIDLIST, sPath As String lRet = SHGetSpecialFolderLocation(100&, CSIDL_PERSONAL, IDL) If lRet = 0 Then sPath = String$(512, Chr$(0)) lRet = SHGetPathFromIDList(ByVal IDL.mkid.cb, ByVal sPath) Rep_Documents = Left$(sPath, InStr(sPath, Chr$(0)) - 1) Else Rep_Documents = vbNullString End If End Function

---

Sample call: In a leaf paste a button and the button code.

Code:

---

Private Sub CommandButton1_Click() Cells(5, 2) = Rep_Documents() End Sub

---

More single VB6
Sub VB6, you just have to exploit the environment variable UserProfile:

Code:

---

"%UserProfile%\My documents

---

Without including quotes.