Solved

Running command withing Excel using cell vaule as parameters

Posted on 2012-03-22
7
997 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

A quick guide on how to use Group Policy to create a custom power plan and set it active on Windows 7.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

803 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