[VBA/VB6] My Documents + Environment Variables

Getting started

As displayed in Windows Explorer, the My Documents folder appears to be in the root, but it is not the case. It is located in a subdirectory of C:\Documents and Settings. The problem is that this subdirectory takes the user name and is changed not only from one PC to another, but also on PC supporting multi users.

The following codes allow you to access the My Documents folder by default regardless of the user logged on.

With VBA

Just paste the following code in a general module:

Option Explicit
Private Type SHITEMID
    cb As Long
    abID As Byte
End Type
Private Type ITEMIDLIST
    mkid As SHITEMID
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

To call the function, simply create a  button and paste in the following code:
Private Sub CommandButton1_Click()
    Cells(5, 2) = Rep_Documents()
End Sub

With VB6

Under VB6, use the environment variable UserProfile (works also with VBA).
Dim sPathUser as String
sPathUser = Environ$("USERPROFILE") & "\mes documents\"
MsgBox sPathUser

Environment Functions

The Environ$ function is use to receive the value of an environment variable.
For example, when you put the WINDIR command (Windows), you would have obtained the folder where Windows is installed (C:\Windows\).
These variables can use in batch file, through the Run utility and in a programming environment such as VB and VBA.
Typing % UserProfile%, it will bring in the current user file.
Typing % UserProfile%\My Documents" it will bring in the My Documents folder.
Typing %WinDir% for the Windows folder... or%tmp% to access temporary files, etc ...

Windows Variables

Variables for default user
TEMP temporary directory
TMP temporary directory
System variables
ComSpec variable path for the command prompt.
FP_NO_HOST_CHECK?
NUMBER_OF_PROCESSORS?
OS Returns OS in use.
Path?
PATHEXT?
PROCESSOR_ARCHITECTURE Returns the processor architecture (x86 etc ...)
PROCESSOR_IDENTIFIER Returns the processor id.
PROCESSOR_LEVEL?
PROCESSOR_REVISION Returns the number of processor revision.
TEMP temporary directory.
TMP temporary directory.
windir folder where Windows is installed.
SystemRoot folder where Windows is installed.

Note:
If you are log in as an administrator, the changes can be made through the Control Panel.