?
Solved

Running command withing Excel using cell vaule as parameters

Posted on 2012-03-22
7
Medium Priority
?
1,043 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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