Excel中的VBA - 获取一个用户的LDAP字符串正确 [英] Excel VBA - Getting a users LDAP string correct

问题描述

我不知道什么是我错了，但我不能让这串吧！我已经得到了用户信息这个Excel工作表，我想通过LDAP连接到广告，但我得到这个自动化错误'-2147217900（80040E14）'，这可能意味着有一个在LDAP字符串中的语法错误。现在，我用这个功能来拿起用户的专有名称。然后我返回，并试图通过adoConnection.Execute通过它。

返回的LDAP字符串看起来是这样的：

<$p$p><$c$c><LDAP://CN=Bowie\,David,OU=Geniouses,OU=Music,DC=MasterDomain,DC=local>;ADsPath;subtree

在code是这样的：

  ldapStr =＆LT; LDAP：//＆放大器; getUsersDN（dbowie）及取代; Active Directory路径;子树
 

这样的功能：

 公共功能getUsersDN（BYVAL strUserName中作为字符串）
常量ADS_SCOPE_SUBTREE = 2

设置objConnection =的CreateObject（ADODB.Connection）
设置objCommand =的CreateObject（ADODB.Command）
objConnection.Provider =ADsDSOObject
objConnection.OpenActive Directory提供
设置objCommand.ActiveConnection = objConnection

objCommand.Properties（大小）= 1000
objCommand.Properties（SearchScope的）= ADS_SCOPE_SUBTREE

objCommand.CommandText = _
    选择的distinguishedName从LDAP：// DC = MasterDomain，DC =本地与＆amp; _
        WHE​​RE objectCategory属性='用户'＆放大器; _
            而sAMAccountName ='＆放大器; strUserName中和放大器; '
设置objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
做直到objRecordSet.EOF
    strDN = objRecordSet.Fields（distinguishedName来）。值
    getUsersDN = strDN
    objRecordSet.MoveNext
循环
端功能
 

解决方案

事实上，我自己的答案用阿扎德Scriptomatic的：）

code现在看起来是这样的：

 设置objRootDSE = GetObject的（LDAP：// RootDSE的）
        昏暗strQuery作为字符串
        strQuery =（LDAP：//＆放大器; getUsersDN（dbowie））

        设置objItem = GetObject的（strQuery）

        ************
        *端连接到一个对象*
        ************

        objItem.Put说明，测试
        objItem.SetInfo

    公共职能getUsersDN（BYVAL strUserName中作为字符串）
        常量ADS_SCOPE_SUBTREE = 2

        设置objConnection =的CreateObject（ADODB.Connection）
        设置objCommand =的CreateObject（ADODB.Command）
        objConnection.Provider =ADsDSOObject
        objConnection.OpenActive Directory提供
        设置objCommand.ActiveConnection = objConnection

        objCommand.Properties（大小）= 1000
        objCommand.Properties（SearchScope的）= ADS_SCOPE_SUBTREE

        objCommand.CommandText = _
            选择的distinguishedName从LDAP：// DC = myDomain的，DC =本地与＆amp; _
                WHE​​RE objectCategory属性='用户'＆放大器; _
                    而sAMAccountName ='＆放大器; strUserName中和放大器; '
        设置objRecordSet = objCommand.Execute

        objRecordSet.MoveFirst
        做直到objRecordSet.EOF
            strDN = objRecordSet.Fields（distinguishedName来）。值
            getUsersDN = strDN
            objRecordSet.MoveNext
        循环
端功能
 

I don't know what's wrong with me, but I can't get this string right! I've got this Excel sheet of user information and I want to connect to AD via LDAP, but I get this automation error '-2147217900 (80040e14)', which probably means there's a syntax error in the LDAP string. Now, I use this function to pick up the users distinguished name. Then I return that and try to pass it through adoConnection.Execute.

The returned LDAP string looks like this:

<LDAP://CN=Bowie\,David,OU=Geniouses,OU=Music,DC=MasterDomain,DC=local>;ADsPath;subtree

The code looks like this:

ldapStr = "<LDAP://" & getUsersDN("dbowie") & ">;ADsPath;subtree"

Function like this:

Public Function getUsersDN(ByVal strUsername As String)
Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
    "SELECT distinguishedName FROM 'LDAP://dc=MasterDomain,dc=local' " & _
        "WHERE objectCategory='user' " & _
            "AND sAMAccountName='" & strUsername & "'"
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    strDN = objRecordSet.Fields("distinguishedName").Value
    getUsersDN = strDN
    objRecordSet.MoveNext
Loop
End Function

解决方案

I actually got the answer myself using AzAD Scriptomatic :)

Code now looks like this:

        Set objRootDSE = GetObject("LDAP://rootDSE")
        Dim strQuery As String
        strQuery = ("LDAP://" & getUsersDN("dbowie"))

        Set objItem = GetObject(strQuery)

        '***********************************************
        '*         End connect to an object           *
        '***********************************************

        objItem.Put "description", "test"
        objItem.SetInfo

    Public Function getUsersDN(ByVal strUsername As String)
        Const ADS_SCOPE_SUBTREE = 2

        Set objConnection = CreateObject("ADODB.Connection")
        Set objCommand = CreateObject("ADODB.Command")
        objConnection.Provider = "ADsDSOObject"
        objConnection.Open "Active Directory Provider"
        Set objCommand.ActiveConnection = objConnection

        objCommand.Properties("Page Size") = 1000
        objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

        objCommand.CommandText = _
            "SELECT distinguishedName FROM 'LDAP://dc=myDomain,dc=local' " & _
                "WHERE objectCategory='user' " & _
                    "AND sAMAccountName='" & strUsername & "'"
        Set objRecordSet = objCommand.Execute

        objRecordSet.MoveFirst
        Do Until objRecordSet.EOF
            strDN = objRecordSet.Fields("distinguishedName").Value
            getUsersDN = strDN
            objRecordSet.MoveNext
        Loop
End Function

这篇关于Excel中的VBA - 获取一个用户的LDAP字符串正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！