Solved

Running command withing Excel using cell vaule as parameters

Posted on 2012-03-22
7
1,003 Views
Last Modified: 2012-03-29
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  
-----
 display
 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.
Sample.xlsx
0
Comment
Question by:principiamanagement
  • 4
  • 3
7 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 37755703
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.

Regards,

Rob.

Sample.xlsm
0
 

Author Comment

by:principiamanagement
ID: 37759849
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.....
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 37763629
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")
or
=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:
http://www.kouti.com/tables/userattributes.htm

Regards,

Rob.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:principiamanagement
ID: 37778002
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") ?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 37779686
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)
    Else
    ' 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
                        Else
                            strReturnVal = strReturnVal & ", " & strValue
                        End If
                    Next
                Else
                    strReturnVal = adoRecordset.Fields(intCount).Value
                End If
            Else
                If IsArray(adoRecordset.Fields(intCount).Value) Then
                    For Each strValue In adoRecordset.Fields(intCount).Value
                        strReturnVal = strReturnVal & ", " & strValue
                    Next
                Else
                    strReturnVal = strReturnVal & ", " & adoRecordset.Fields(intCount).Value
                End If
            End If
        Next
        ' Move to the next record in the recordset.
        adoRecordset.MoveNext
    Loop
 
    ' Clean up.
    adoRecordset.Close
    ADOConnection.Close
    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:
=RunCommand(B2)

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"
    Wend
    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.

Regards,

Rob.
0
 

Author Closing Comment

by:principiamanagement
ID: 37780645
Thanks a lot Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 37784444
No problem. Thanks for the grade.

Rob.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

OfficeMate Freezes on login or does not load after login credentials are input.
When you try to extract and to view the contents of a Microsoft Update Standalone Package (MSU) for Windows Vista, you cannot extract the files from the MSU. Here we are going to explain how to extract those hotfix details without using any third pa…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question