Solved

Running command withing Excel using cell vaule as parameters

Posted on 2012-03-22
7
976 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

First some basics on Windows 7 Backup.  It has 2 components one is a file based backup which is stored in .zip files each zip is split at around 200 Megabytes and there is the Image Backup which is as the name implies a total image of the partition …
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now