Printable View
Hello friend i just installed Ms office 2007 i made some excel document in that. I want to know that if it is probable for using Microsoft Excel to make a Ldap query and if its probable how can i make that LDAP query to run in MS excel dosument, or if someone have any suggestion to my quote to put me in the right way as soon as feasible to you. Please get it soon.
Try out this query it may help you out.
Code:Option Explicit
Private Const tDomain As String = "MyDomain" ‘ your current domain
Public Sub ShowADPrinters()
Dim tConn As ADODB.Connection
Dim tCmd As ADODB.Command
Dim rs11 As ADODB.Recordset
Dim Cell1 As Range
Dim i As Long
Dim tPrinterList() As String
Application1.ScreenUpdating = False
On Error GoTo ErrHandler
Set tConn = New ADODB.Connection
Set tCmd = New ADODB.Command
tConn.Provider = "ADsDSOObject"
tConn.Open "Active Directory Provider"
' Create a command object for this connection.
Set tCmd.ActiveConnection = tConn
' Compose a search string.
tCmd.CommandText = "SELECT printerName, printColor " & _
"FROM 'LDAP://DC=" & tDomain & ",DC=local' " & _
"WHERE objectCategory='printQueue' "
' Execute the query.
Set rs11 = oCmd.Execute
rs11.MoveFirs11t
ReDim tPrinterList(rs11.RecordCount + 1, 1)
i = 0
Do While Not rs11.EOF
'Debug.Print rs11!printerName
tPrinterList(i, 0) = rs11!printerName
tPrinterList(i, 1) = rs11!printColor
rs11.MoveNext
i = i + 1
Loop
Set Cell1 = Range("A1")
Cell1.Value = "Printer Name"
Cell1.Offset(0, 1).Value = "Colour Printer"
For i = 0 To UBound(tPrinterList)
Cell1.Offset(i + 1, 0) = tPrinterList(i, 0)
Cell1.Offset(i + 1, 1) = tPrinterList(i, 1)
Next
ErrHandlr:
Set oCmd = Nothing
tConn.Close
Set tConn = Nothing
End Sub
I think this query also works according to your requirement.
Code:Dim y
Dim strSpacer1
Sub ldap1()
Sheets("SheetXYZ").Select
On Error Resume Next
Set tcon = CreateObject("ADODB.Connection")
Set tcmd = CreateObject("ADODB.command")
Set trst = CreateObject("ADODB.RecordSet")
'defines the type of DB we are connecting to
tcon.Provider = "ADsDSOObject"
tcon.Open
tcmd.ActiveConnection = tcon
tcmd.Properties("Page Size") = 20000
'Submit the query
tcmd.CommandText = "<LDAP1://DC=capitol,DC=local>;(cn=Gary Stockton);name,
ADsPath"
Set trst = tcmd.Execute
y = 2
Do Until trst.EOF
Range("a" & y).Select
Selection.Font.Bold = True
Range("a" & y).Value = trst.Fields("name")
Range("b" & y).Value = trst.Fields("ADsPath")
Range("c" & y).Value = trst.Fields("class")
ListGroups
trst.MoveNext
y = y + 1
Loop
End Sub
Private Sub ListGroups()
On Error Resume Next
Set TObject = GetObject(Range("B" & y).Value)
objMemberOf = TObject.GetEx("MemberOf")
strSpacer1 = strSpacer1 & " "
For Each objGroup In TObject.memberOf
If Not objGroup = Empty Then
y = y + 1
strQuery = "LDAP1://" & objGroup
Set TObject = GetObject(strQuery)
Range("a" & y).Value = strSpacer1 & Mid(TObject.Name, 4,
Len(TObject.Name) - 3)
Range("b" & y).Value = TObject.ADsPath
ListGroups
End If
Next
strSpacer1 = Left(strSpacer1, Len(strSpacer1) - 6)
End Sub
MS-Excel LDAP Search is an MS-Excel add-in that permit LDAP searches to be made from within MS-Excel this generally use within queries, and the ensuing data to be directly imported from the index server to the MS-Excel document. ELS can be used to add LDAP data to presented data in an MS-Excel document on a row-by-row basis (see Figure 1), or colonize a blank sheet based on a custom query. Though there some limitation in using this quey ELS was designed for a quite essential LDAP surroundings. Some characteristic that are not hold are SSL, certificate verification, and recommend following. I put jointly a pair of flash demo movies that show classic ELS practice for a running a query to fill in a empty Excel sheet and for adding up new row data to existing Excel entries.