Excel 2000 - Identifying Login and Computer Name

[stephenj789]

Is the following possible?:

I have file A.xls. In File A.xls I have some code that goes out and grabs the computer name and login of somebody who is using file B.xls.

I can create a field in B.xls that gives the current users login and computername. However, I would have to place this code into many files, which I don't want to do. Any suggestions?

I don't think it is possible, but I thought that it wouldn't hurt to ask.

 

[firefytr]

Hi there,

Check out Ivan Moala's site here:

http://www.xcelfiles.com/IsFileOpen.html

HTH

-----------
Regards,
Zack Barresse

 

[stephenj789]

The code referred to in the link works, but it gives the user name located under Tools -> Options 'General' tab of Excel.

The reason I need the code is that sometimes when we try to open a file, the person using it hasn't put their name in their Excel options (it simply says CorpUser). If I have the Windows login and/or computername, finding the person who is in the file becomes possible.

However, thanks for the link to the code, as I think it will be useful for other purposes.

 

[firefytr]

Well, as a simple workaround, if you're using Windows machines, you can just check the special folders (My Documents) and check the name that way. It's a sloppy workaround, but since you should already have the filename, that should work.

Public Function DesktopAddress() As String
    DesktopAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop")
End Function

Sub testingthis()
    Dim strUser As String
    strUser = DesktopAddress
    strUser = Left(strUser, InStrRev(strUser, Application.PathSeparator) - 1)
    strUser = Right(strUser, Len(strUser) - InStrRev(strUser, Application.PathSeparator))
    MsgBox strUser
End Sub

-----------
Regards,
Zack Barresse

 

[xlbo]

both of these are environment variables. Listing of variables and how to call them can be obtained via this FAQ: faq707-4296

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

I didn't think the Environ() function pertained to network locations Geoff??

-----------
Regards,
Zack Barresse

 

[xlbo]

lol - it doesn't - misread the question initially

apologies stephen

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bpeirson]

Here is some code I recieved from Eng-Tips.com or Tek-Tips.com. They will return the logged in user, the computer name and MAC address.

Private Declare Function GetComputerName Lib "kernel32" _
  Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _
  As Long

Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) _
  As Long

Function NameOfComputer()
'   Returns the name of the computer
    Dim ComputerName As String
    Dim ComputerNameLen As Long
    Dim Result As Long
    ComputerNameLen = 256
   ComputerName = Space(ComputerNameLen)
    Result = GetComputerName(ComputerName, ComputerNameLen)
    If Result <> 0 Then
        NameOfComputer = Left(ComputerName, ComputerNameLen)
 
    Else
        NameOfComputer = "Unknown"
    End If
End Function

Function NameofUser() As String
'   Returns the name of the logged-in user
    Dim Buffer As String * 100
    Dim BuffLen As Long
    BuffLen = 100
    GetUserName Buffer, BuffLen
    NameofUser = Left(Buffer, BuffLen - 1)
End Function

Public Function GetMACAddress()
'
'  Returns the (first) MAC address for the computer.
'  Uses "-" as its presentation delimiter.
'
Dim Net As Object
Dim SH As Object
Dim FSO As Object
Dim TS As Object
Dim Data As String
Dim MACAddress As String
'
On Error GoTo ErrorHandler
'
Set Net = CreateObject("wscript.network")
'
Set SH = CreateObject("wscript.shell")
SH.Run "%comspec% /c nbtstat -a " _
    & Net.ComputerName & " > c:\nbtstat.txt", 0, True
Set SH = Nothing
Set Net = Nothing
'
Set FSO = CreateObject("scripting.filesystemobject")
Set TS = FSO.opentextfile("c:\nbtstat.txt")
MACAddress = ""
Do While Not TS.AtEndOfStream
    Data = UCase(Trim(TS.readline))
    If InStr(Data, "MAC ADDRESS") Then
        MACAddress = Right(WorksheetFunction.Clean(Data), 17)
        Exit Do
    End If
Loop
'
TS.Close
Set TS = Nothing
FSO.deletefile "c:\nbtstat.txt"
Set FSO = Nothing
'
If Len(MACAddress) < 2 Then GoTo ErrorHandler
GetMACAddress = MACAddress
Exit Function
'
ErrorHandler:
GetMACAddress = "Error in GetMACAddress function"
End Function

The functions can be used within the worksheet or within VBA code.

I use the username and computername functions extensively. I do not use the MAC address function much.

If you need help implementing any of these functions reply to this thread.

 

[bpeirson]

Oops, to clarify my earlier post, I have the functions in an Excel add-in which is loaded onto all versions of Excel in our office. These functions are then available to any new spreadsheet made provided the add-in is activated.

 

[sozzer]

To approach this from maybe a different angle, what OS are you running?

 

[stephenj789]

Windows 2000 Pro. The original link provided by firefytr does what I want it to do (using file A, brings back user data from file B), but it brings back data from Excel, when what I want is the Windows computer name and username. I don't think I am skilled enough to incorporate some of the code listed later in this thread to make it do what I want it to do. Is it even possible to bring back the computer name and username from another file that is in use, just using VBA?

 

[bpeirson]

Excel does not save the logged in username for later retrieval. Excel will only save the name of the registered user as you have discovered. Also Excel will overwrite the username when a new user saves the file.

I have tracked logged in users in the past by using VBA code which writes the username to a hidden sheet on the workbook.

All of the workbooks which I needed this information for were generated from a template. The template included the appropriate code. If a new blank workbook was created then no code was in place to write the username.

If you are using templates add the code to each template for future use. To insure that all users used the same copy of the template, I linked to the template from an add-in which creates its own menu on the standard toolbar.

If your users are creating new blank workbooks (without the VBA code) a workaround can be done by using an add-in for creating a blank workbook and then removing the original button on the toolbar which currently starts a blank workbook.

All of this assumes that you have access to each workstation and the number of workstations would allow you to install the appropriate add-ins to all workstations.