0

I have an issue where I have a list of AD usernames (retrieved from a server log - representing colleagues who are using software they shouldn't be at the time), but I need a way of looking up (from AD) what their email address (or full name, if email is not possible/reliable), so that I or others can contact those users to ask them to stop.

The tool I have already, that gathers the list of usernames from the server logs, is in Excel (thus VBA), so I ideally need a solution that works from VBA as well. Essentially, is there any way to pass an arbitrary AD username (NOT the current user) and return either the FullName or Email from AD? Obviously it is fairly simple if I want to retrieve the email of the current user, but that is not the question I have asked (I say this purely because there are LOTS of forum threads on retrieving Current User info from AD).

Finally, I suspect this should be possible in principle, because it is trivial to do in Python using win32net.NetUserGetInfo - so the underlying API must be there, I just don't know how to call it from VBA.

Edit: I can achieve essentially what I need in CMD as well, with the line:

net user %userid% /DOMAIN | find /I "Full name"

Thus is it possible to call the above line of CMD from within VBA (without high risk of corporate antivirus blocking it, because this has happened before when actually spawning hidden CMD shells from VBA)?

RobBaker
  • 137
  • 11
  • Perhaps vlookup or index with match. – Solar Mike Feb 24 '23 at 08:38
  • I *only* have the username list, not the Full Names of these users. So there is no (local) data to vlookup from... – RobBaker Feb 24 '23 at 09:16
  • So if you don't have a common value to use, what do you expect? – Solar Mike Feb 24 '23 at 09:17
  • I don't think you understand my question, Solar Mike - I have a list of AD usernames (most of which are cryptic as to who they actually are e.g. "TanneL01"). I want to use something like the CMD "net user" command to look up the user's Full Name from the AD domain. This is trivial in CMD or Python. I am asking for help with calling CMD from VBA and parsing the result into a cell, for example. – RobBaker Feb 24 '23 at 09:34
  • Hmm, I don't think you understand your own question. I use vlookup, hlookup, lookup xlookup and index with match as needed to extract relevant result from a data table - but in all situations you need a value common to index with. – Solar Mike Feb 24 '23 at 09:37
  • 1
    The most upvoted answer here works - https://stackoverflow.com/questions/7805856/vba-retrieve-the-name-of-the-user-associated-with-logged-username – user10186832 Feb 24 '23 at 09:58
  • Thanks user10186832, that is probably a more complete answer, but in the meantime I found a simple hack that works. I will save your link though, because if my 'dirty' solution ever stops working (corporate antivirus, I'm looking at you), I will give it a go! – RobBaker Feb 24 '23 at 10:00
  • @SolarMike, I simply don't think you understand what AD means here - Active Directory. This is data stored in the corporate network, not a table of data I have to hand in an Excel file. I ended up getting there by calling the AD API via 'net user' in a CMD.exe shell - this has nothing to do with vlookup (which I use a lot, so I am familiar). – RobBaker Feb 24 '23 at 10:02
  • 1
    Try this post, I have done similar in the past but don't have AD because I am at home :) ... https://stackoverflow.com/questions/46925946/get-department-of-user-based-on-fullname-in-active-directory – user10186832 Feb 24 '23 at 14:52

1 Answers1

0

I have solved this in a fairly inelegant way, so am open to better answers, but I have done it by a combination of calling "net user" directly using CMD.exe (from VBA using Shell) and then another SO answer for parsing the output from that Shell command from STDOUT (Capture output value from a shell command in VBA?).

The important bits of my code are, in my main VBA sheet (gives you context for 'Me') code:

' Note d_offenders is a dictionary of AD usernames (the value stored is a list of software they have open, so not relevant to this solution)
Dim key As Variant
For Each key In d_offenders.Keys
    Me.Cells(start_row + i, output_col).Value2 = key
    Me.Cells(start_row + i, output_col + 1).Value2 = get_name(CStr(key))
    Me.Cells(start_row + i, output_col + 2).Value2 = d_offenders(key)
    i = i + 1
Next key

Then I use the following two functions to define how the full name is retrieved:

Private Function get_name(uname As String) As String
    Dim res As String
    res = ShellRun("cmd.exe /C net user " & uname & " /DOMAIN | find /I ""Full name""")
    If res > vbNullString Then get_name = Right(res, Len(res) - 29) Else get_name = "Not found."
End Function

Public Function ShellRun(sCmd As String) As String

    'Run a shell command, returning the output as a string

    Dim oShell As Object
    On Error GoTo errHandler
    Set oShell = CreateObject("WScript.Shell")

    'run command
    Dim oExec As Object
    Dim oOutput As Object
    Set oExec = oShell.Exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> vbNullString Then s = s & sLine & vbCrLf
    Wend

    ShellRun = s
    Exit Function
errHandler:
    ShellRun = vbNullString
End Function

As I said, slightly clunky with the (temporary) CMD windows popping up, but it works and I end up with a list of:

  • usernames
  • their full name from AD
  • the software packages they have open
RobBaker
  • 137
  • 11