• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get username (who open my excel file)

I

IceFrogBG

Member
Hi all,
Now I want to make a file (include VBA) to check some file on server.
And I want display a text on my excel file :
Ex : I try to get name but I can get user ID only.
Anyone can give me some method.
Thank you so much.
 

Attachments

  • Username.jpg
    Username.jpg
    241 KB · Views: 15
Code: 
Function GetUserName() As String
Dim lpBuff As String * 25
Get_User_Name lpBuff, 25
GetUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
End Function
I use this Function but it can not get the name.
 
I am not sure what you are after.

The method that you posted would not work for others as you did not include the API function.

If you want the environment variable value for username then:
Code: 
MsgBox Environ("username")

This will get your full name.
Code: 
Sub Test_getFullname()
  MsgBox getfullname
End Sub

'https://msdn.microsoft.com/en-us/library/aa390386(v=vs.85).aspx#enumerating_wmi_using_vbscript
Function getfullname() As String
  Dim objAllNames As Object
  On Error Resume Next
  Set objAllNames = GetObject("Winmgmts:").instancesof("win32_networkloginprofile")
  For Each objIndName In objAllNames
    getfullname = objIndName.FullName
  Next
End Function
 
Kenneth Hobson said:
I am not sure what you are after.

The method that you posted would not work for others as you did not include the API function.

If you want the environment variable value for username then:
Code: 
MsgBox Environ("username")

This will get your full name.
Code: 
Sub Test_getFullname()
  MsgBox getfullname
End Sub

'https://msdn.microsoft.com/en-us/library/aa390386(v=vs.85).aspx#enumerating_wmi_using_vbscript
Function getfullname() As String
  Dim objAllNames As Object
  On Error Resume Next
  Set objAllNames = GetObject("Winmgmts:").instancesof("win32_networkloginprofile")
  For Each objIndName In objAllNames
    getfullname = objIndName.FullName
  Next
End Function
Click to expand...
Thank you , I try your code it can get the data which I need.
But there is one problem , It took long time (about 30 seconds).
So is there any way faster ?
 
Not really through vba directly I'd imagine.

Personally, I'd create command line batch file with following script.
Code: 
@echo off
for /f "tokens=2*" %%a in ('net user "%Username%" /domain ^| find /i "Full Name"') do set DisplayName=%%b
echo %DisplayName% > C:\test\fullname.txt

Then using Shell() run the batch file and read text file from Excel.
 
IceFrogBG said:
Thank you , I try your code it can get the data which I need.
But there is one problem , It took long time (about 30 seconds).
So is there any way faster ?
Click to expand...
Hi ,

Which macro took 30 seconds to run to completion ?

I ran the macro named Test_getFullname , and it displayed the user name instantaneously.

Narayan
 
Depends on environment I'd suppose, took about 25 sec first run on my comp.
Then ran much faster on subsequent run, about 2 sec.
 
I don't have a network on this computer so I don't know if this is faster. Speed may depend on how many users on your network and network server limits.
Code: 
Function getfullname() As String
  Dim objIndName As Object, objAllNames As Object
  On Error Resume Next
  Set objAllNames = GetObject("Winmgmts:").instancesof("win32_networkloginprofile")
  For Each objIndName In objAllNames
    getfullname = objIndName.FullName
    If getfullname <> "" Then Exit For
  Next
End Function
 
Tested on couple of different users on my network machine. Took about 4 to 5 sec avg on first run. Then about avg 1.5 sec on subsequent runs with new code.
 
Most tests will usually be longer for the 1st run. In this case, the 3rd run was usually longer for me. For those on big networks, time will be much more.

Method 1 as shown in post #3 and #8:
Code: 
'0.04s
Sub Test_getFullName()
  Dim d#, s$
  d = Timer
  s = getFullName
  d = Timer - d
  Debug.Print s, d
  MsgBox s & " found " & d & " in seconds."
End Sub

Function getFullName() As String
  Dim objIndName As Object, objAllNames As Object
  On Error Resume Next
  Set objAllNames = GetObject("Winmgmts:").instancesof("win32_networkloginprofile")
  For Each objIndName In objAllNames
    getFullName = objIndName.FullName
    If getFullName <> "" Then Exit For
  Next
End Function
Somewhat similar to post #5 was somewhat longer. The best test is on a big network but you "may" have to replace the s string's DOMAIN with your domain name. You should be able to get it by Environ("userdomain") or use that in the string as I did for UserName. I can't test that for 6 days though.
Code: 
'0.17s
Sub ws()
  Dim s$, d#, a
  d = Timer
  's = CreateObject("Wscript.Shell").Exec("net user ""%USERNAME%"" /DOMAIN | FIND /I ""Full Name""").StdOut.readall
  s = CreateObject("Wscript.Shell").Exec("cmd /c net user ""%USERNAME%"" | FIND /I ""Full Name""").StdOut.readall
  s = Replace(Right(s, Len(s) - 29), vbCrLf, "")
  d = Timer - d
  Debug.Print s, d
  MsgBox s & " found " & d & " in seconds."
End Sub
 
NARAYANK991 said:
Hi ,

Which macro took 30 seconds to run to completion ?

I ran the macro named Test_getFullname , and it displayed the user name instantaneously.

Narayan
Click to expand...
Code: 
Private Sub CommandButton7_Click()
Call Test_getFullname
End Sub
Sub Test_getFullname()
  MsgBox getfullname
End Sub
Function getfullname() As String
  Dim objIndName As Object, objAllNames As Object
  On Error Resume Next
  Set objAllNames = GetObject("Winmgmts:").instancesof("win32_networkloginprofile")
  For Each objIndName In objAllNames
    getfullname = objIndName.FullName
    If getfullname <> "" Then Exit For
  Next
End Function
I try this function , it took long time at the first times , and the second time it faster.
 
Chihiro said:
Depends on environment I'd suppose, took about 25 sec first run on my comp.
Then ran much faster on subsequent run, about 2 sec.
Click to expand...
Hi Chihiro,
I'm too,the first run is so long after that it is faster.
Still now I'm trying some method.
Thanks.
 
Kenneth Hobson said:
Most tests will usually be longer for the 1st run. In this case, the 3rd run was usually longer for me. For those on big networks, time will be much more.

Method 1 as shown in post #3 and #8:
Code: 
'0.04s
Sub Test_getFullName()
  Dim d#, s$
  d = Timer
  s = getFullName
  d = Timer - d
  Debug.Print s, d
  MsgBox s & " found " & d & " in seconds."
End Sub

Function getFullName() As String
  Dim objIndName As Object, objAllNames As Object
  On Error Resume Next
  Set objAllNames = GetObject("Winmgmts:").instancesof("win32_networkloginprofile")
  For Each objIndName In objAllNames
    getFullName = objIndName.FullName
    If getFullName <> "" Then Exit For
  Next
End Function
Somewhat similar to post #5 was somewhat longer. The best test is on a big network but you "may" have to replace the s string's DOMAIN with your domain name. You should be able to get it by Environ("userdomain") or use that in the string as I did for UserName. I can't test that for 6 days though.
Code: 
'0.17s
Sub ws()
  Dim s$, d#, a
  d = Timer
  's = CreateObject("Wscript.Shell").Exec("net user ""%USERNAME%"" /DOMAIN | FIND /I ""Full Name""").StdOut.readall
  s = CreateObject("Wscript.Shell").Exec("cmd /c net user ""%USERNAME%"" | FIND /I ""Full Name""").StdOut.readall
  s = Replace(Right(s, Len(s) - 29), vbCrLf, "")
  d = Timer - d
  Debug.Print s, d
  MsgBox s & " found " & d & " in seconds."
End Sub
Click to expand...
Hello Mr Kenneth Hobson,
I'm very thanks for your support.
I don't know I use computer on my company has any point different your computer.
But the Sub ws() was not working.
And once more this excel file I only check current user is using.
It is on one computer (not on server)
 
Chihiro said:
Not really through vba directly I'd imagine.

Personally, I'd create command line batch file with following script.
Code: 
@echo off
for /f "tokens=2*" %%a in ('net user "%Username%" /domain ^| find /i "Full Name"') do set DisplayName=%%b
echo %DisplayName% > C:\test\fullname.txt

Then using Shell() run the batch file and read text file from Excel.
Click to expand...
I create and run this bat file , it can work.
Thanks.
 
IF on a network, the /domain or the domain name, e.g., /Sooner, might be needed. As I said, I won't be able to check that until Dec. 13. You can easily test that. I left that s commented so just uncomment and comment out the s line that I used.

Other than that method (NET USER) and the WMI method, I don't know of another reliable method.
 
You must log in or register to reply here.
Back
Top