VBScript for querying eDirectory

16 Dec

So i needed a way to lookup values in an Excell Spreadsheet against a Novell IDM Value (eDirectory Tree). Below follows the code for searching for values in Column A and if present in eDirectory than put a value in Column B to that effect :

(Note: Im using 389 and not 636, code needs adjusting for SSL connectivity. Also, replace all <value> with values specific to your environment) 

Lastly, be carefull of the strain that a large query (100000’s) of comparison records against your eDir Tree can cause. Ideally, you’d want to store this in a Hashtable/ADORecordset and than search locally, but this is a quick and dirty meathod.


Public Sub CheckEmail()
‘Option Explicit
‘On Error Resume Next
Dim val As String
Dim rng As Range
Dim i As Integer

‘ADO Constants
Const adOpenStatic = 3
Const adLockOptimistic = 3
‘The value below required for the connection string to read a text file
Const adCmdText = &H1
Const objectExists = -2147019886
Const failToOpenObject = -2147016646
Const InvalidUseOfNull = 94
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Provider = “ADsDSOObject”
objConnection.Open “Active Directory Provider”, “CN=<username>,o=<container>”, “<password>”
Set objCommand = CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConnection
count = 1
Set rng = Range(“A1:A2146”)
For i = rng.Rows.count To 1 Step -1
    val = rng.Cells(i, 1).Value
   objCommand.CommandText = _
    “LDAP://<server>/OU=<ou>,O=<container>>;” & _
        “(&(objectClass=AUCoreIdentityAUX)(mail=” & val & “));” & _
    Set objRecordset = objCommand.Execute
    If objRecordset.EOF Then
        Cells(i, 2).Value = “no”
        ‘MsgBox “No records returned for the current objCommand.CommandText :” & vbCrLf & objCommand.CommandText
          Cells(i, 2).Value = “yes”
    End If

End Sub


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: