Running command withing Excel using cell vaule as parameters

Please refer the attached sample excel sheet. I have Domain login username in Column "A". I would like to run a command in Column "B". Which takes values from Column "A" as parameter.  Basically it runs Dsquery user command to get displayname of specific user.

It gives out put like below  
 Lastname1, Firstname1
dsget succeeded.

I would like to have Only the  "Lastname1, Firstname1" to be updated as output in column “C”.

Currently I am keeping Column “B” in batch file and pipe the result in to a text file and the removing “display” and “dsget succeeded”. Then pasting the results in to Column “C”. This is time consuming and might result in error if other users do it. So I want everything to be done by excel.

Let me know how the command in column “B” can be executed within excel and the output comes in Column “C” as a display name of the user only.
Who is Participating?
RobSampsonConnect With a Mentor Commented:
Hi, see the attached spreadsheet.  This uses VBA to perform a directory lookup, instead of dsquery, so you can just plug in the function parameters into the cells in columns c and d and it will return the results to that cell.

I have provided a sample on row 2.  You can just fill down the formula.



principiamanagementAuthor Commented:
Thanks Rob. It works like a charm....Couple of queires

1- How I can view the macro you have placed in ....I am in 2010 ....
2- If i need other AD properties like group membership, phone number, Address and all ...then How i can perform this query.....
I used Excel 2007....if you press ALT + F11 you should see the VBE, and the code modules on the left had side.

If you want to retrieve other values into separate cells, just use this formula in another cell:
=Get_LDAP_User_Properties("user", "samaccountname", A2, "telephoneNumber")
=Get_LDAP_User_Properties("user", "samaccountname", A2, "streetAddress,l,st,postalCode")

to have them together.

Group membership is a bit different, since it's an array property, but you can use
=Get_LDAP_User_Properties("user", "samaccountname", A2, "memberOf")

Remember that A2 in each of those examples is the cell that contains the samaccountname for that row.

See here for a list of attributes:


Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

principiamanagementAuthor Commented:
I could get that. But as you mentioned the Group list is not coming properly. It is only showing the first group name.

Onething i was always wondering was can i run command in excel (say command in column "B" and the result in Column "C") ?
Hi, I fixed up the function so that it returns all groups:
Public Function Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
    ' This is a custom function that connects to the Active Directory, and returns the specific
    ' Active Directory attribute value, of a specific Object.
    ' strObjectType: usually "User" or "Computer"
    ' strSearchField: the field by which to seach the AD by. This acts like an SQL Query's WHERE clause.
    '             It filters the results by the value of strObjectToGet
    ' strObjectToGet: the value by which the results are filtered by, according the strSearchField.
    '             For example, if you are searching based on the user account name, strSearchField
    '             would be "samAccountName", and strObjectToGet would be that speicific account name,
    '             such as "jsmith".  This equates to "WHERE 'samAccountName' = 'jsmith'"
    ' strCommaDelimProps: the field from the object to actually return.  For example, if you wanted
    '             the home folder path, as defined by the AD, for a specific user, this would be
    '             "homeDirectory".  If you want to return the ADsPath so that you can bind to that
    '             user and get your own parameters from them, then use "ADsPath" as a return string,
    '             then bind to the user: Set objUser = GetObject("LDAP://" & strReturnADsPath)
    ' Now we're checking if the user account passed may have a domain already specified,
    ' in which case we connect to that domain in AD, instead of the default one.
    If InStr(strObjectToGet, "\") > 0 Then
          arrGroupBits = Split(strObjectToGet, "\")
          strDC = arrGroupBits(0)
          strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
          strObjectToGet = arrGroupBits(1)
    ' Otherwise we just connect to the default domain
          Set objRootDSE = GetObject("LDAP://RootDSE")
          strDNSDomain = objRootDSE.Get("defaultNamingContext")
    End If

    strBase = "<LDAP://" & strDNSDomain & ">"
    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set ADOConnection = CreateObject("ADODB.Connection")
    ADOConnection.Provider = "ADsDSOObject"
    ADOConnection.Open "Active Directory Provider"
    adoCommand.ActiveConnection = ADOConnection

    ' Filter on user objects.
    'strFilter = "(&(objectCategory=person)(objectClass=user))"
    strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"

    ' Comma delimited list of attribute values to retrieve.
    strAttributes = strCommaDelimProps
    arrProperties = Split(strCommaDelimProps, ",")

    ' Construct the LDAP syntax query.
    strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
    adoCommand.CommandText = strQuery
    ' Define the maximum records to return
    adoCommand.Properties("Page Size") = 100
    adoCommand.Properties("Timeout") = 30
    adoCommand.Properties("Cache Results") = False

    ' Run the query.
    Set adoRecordset = adoCommand.Execute
    ' Enumerate the resulting recordset.
    strReturnVal = ""
    Do Until adoRecordset.EOF
        ' Retrieve values and display.
        For intCount = LBound(arrProperties) To UBound(arrProperties)
            If strReturnVal = "" Then
                If IsArray(adoRecordset.Fields(intCount).Value) Then
                    For Each strValue In adoRecordset.Fields(intCount).Value
                        If strReturnVal = "" Then
                            strReturnVal = strValue
                            strReturnVal = strReturnVal & ", " & strValue
                        End If
                    strReturnVal = adoRecordset.Fields(intCount).Value
                End If
                If IsArray(adoRecordset.Fields(intCount).Value) Then
                    For Each strValue In adoRecordset.Fields(intCount).Value
                        strReturnVal = strReturnVal & ", " & strValue
                    strReturnVal = strReturnVal & ", " & adoRecordset.Fields(intCount).Value
                End If
            End If
        ' Move to the next record in the recordset.
    ' Clean up.
    Get_LDAP_User_Properties = strReturnVal
End Function

Open in new window

In answer to your question, you *can* run a command, but to get it's output, you need to massage the output to get what you want.

To illustrate, pu this command in B2:
cmd /c ipconfig | find /i "ipv4"

then in C2, enter this formula:

and add this function to your code:
Function RunCommand(strCommand)
    Set objShell = CreateObject("WScript.Shell")
    Set objExec = objShell.Exec(strCommand)
    While objExec.Status = 1
        Application.Wait "00:00:01"
    RunCommand = objExec.StdOut.ReadAll
End Function

Open in new window

That's a simple illustration that doesn't use any string massaging in the code, but if you want to refine it more, you'll need to add some string manipulation.


principiamanagementAuthor Commented:
Thanks a lot Rob.
No problem. Thanks for the grade.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.