Full Exel VBA to extract the Windows Authentication login
=======================================================
Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
ReturnUserName = UCase(Trim(tString))
End Function
========================================================
Short version Exel VBA to extract the Windows Authentication login
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function
=========================================================
I remember using Environ to get the current location of the “My Documents“folder for the current user:
MsgBox Environ("USERPROFILE") + "\My Documents"
So having my memory jarred on the Environ function, I thought I would check VBA help to see what else this Little gem provided. And boy, how disappointing Help was... here is what it looks like: Environ Help.
Not too useful I thought... So I decided to figure it out on my own and loop thru all the arguments possible with Environ.
Copy and run this little routine to see all that Environ offers:
MsgBox Environ("USERPROFILE") + "\My Documents"Public Sub EnvironFunction()
Dim nCount As Integer
nCount = nCount + 1
Do Until Environ(nCount) = ""
Debug.Print Environ(nCount)
nCount = nCount + 1 Loop
End Sub
There are lots of useful things in there including APPDATA, COMPUTERNAME, HOMEDRIVE, HOMEPATH, OS, USERDOMAIN and more... Hopefully you will find it useful and I won't forget about it again.
****Nice to see blogging helps you remember what you forgot and that readers often help writers more than the other way around :)
Here's a complete list (that I know of) of the named arguments for the Environ Function:
Environ arguments
ALLUSERSPROFILE
PATHEXT
APPDATA
PROCESSOR_ARCHITECTURE
AVENGINE
PROCESSOR_IDENTIFIER
CLIENTNAME
PROCESSOR_LEVEL
CommonProgramFiles
PROCESSOR_REVISION
COMPUTERNAME
ProgramFiles
ComSpec
SESSIONNAME
FP_NO_HOST_CHECK
SystemDrive
HOMEDRIVE
SystemRoot
HOMEPATH
TEMP
INCLUDE
TMP
INOCULAN
USERDOMAIN
LIB
USERNAME
LOGONSERVER
USERPROFILE
NUMBER_OF_PROCESSORS
VS71COMNTOOLS
OS
WecVersionForRosebud.FF0
Path
windir
