Getting the User Name

[Otto Moehrbach]

Excel XP & Win XP
I need to get, via VBA, the user name. At least, that's what I thought I
needed. What I really need is the name of the folder under "Documents and
Settings" that the current user is working in. I thought this was the user
name.
On my computer, that folder name is my full name, as in First Last. So
I ran a little one line macro:
MsgBox Application.UserName
That gave me my first name only. Obviously, that line of code is not
looking for the folder name I need that Windows created when I installed
Windows.
Looking through Windows, I see that my user account name is the same as the
folder name I need
Question: How do I get that 'user account'/folder name via VBA? Thanks for
your time. Otto

 

[Vergel Adriano]

Otto,

Application.UserName returns the user name entered in the General tab of
Options. Try

MsgBox Environ("username")

 

[Steve Yandl]

Otto,

Try something like

Sub FindMyFolder()
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H5&)
strMyDocs = objFolder.Self.Path
MsgBox Left(strMyDocs, Len(strMyDocs) - 13)
End Sub

This finds the 'My Documents' folder for the current user and then simply
trims off the "\My Documents" from the right of the path string. If you're
actually wanting the user's desktop folder or something like that, there are
other constants you could use in the Namespace line to retrieve the folder
path more directly.

Steve

 

[BobR]

Steve,

I have a similar issue but need the path to my desktop to put a blank
folder.
How would I put it in code to return the path to my desktop and then put a
folder onto the desktop. We could use blank folder for the name of the
folder?

Thank you
Bob Reynolds

 

[Steve Yandl]

Hi Bob,

Sub FindMyDesk()
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H10&)
strMyDesk = objFolder.Self.Path
MsgBox strMyDesk
End Sub

If you're building the path for the blank folder, don't forget to append
strMyDesk with a final backslash before the name for your new folder.

Steve

 

[Steve Yandl]

Bob,

Just read your question again and see you also wanted to create the folder.
Try this.

_____________________________

Sub FolderToDesk()
Set fso = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H10&)
strMyDesk = objFolder.Self.Path

If Not fso.FolderExists(strMyDesk & "\BlankFolder") Then
Set fldr = fso.CreateFolder(strMyDesk & "\BlankFolder")
End If

Set objShell = Nothing
Set fso = Nothing
End Sub
______________________________

Steve

 

[Otto Moehrbach]

Vergel
Thanks for the help. That works great. Otto

 

[Otto Moehrbach]

Thanks Steve. Otto

Otto,

Try something like

Sub FindMyFolder()
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H5&)
strMyDocs = objFolder.Self.Path
MsgBox Left(strMyDocs, Len(strMyDocs) - 13)
End Sub

This finds the 'My Documents' folder for the current user and then simply
trims off the "\My Documents" from the right of the path string. If
you're actually wanting the user's desktop folder or something like that,
there are other constants you could use in the Namespace line to retrieve
the folder path more directly.

Steve

 

[LabrGuy Bob R]

Thanks for your help Steve.
Bob

Bob,

Just read your question again and see you also wanted to create the
folder. Try this.

_____________________________

Sub FolderToDesk()
Set fso = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H10&)
strMyDesk = objFolder.Self.Path

If Not fso.FolderExists(strMyDesk & "\BlankFolder") Then
Set fldr = fso.CreateFolder(strMyDesk & "\BlankFolder")
End If

Set objShell = Nothing
Set fso = Nothing
End Sub
______________________________

Steve