Excel 2010 VBA, Active Directory

sseba15

New Member
Joined
Jun 9, 2014
Messages
1
Dear all,

I use one of the internet scripts to download data from AD to excel and it works in almost each case but in AD we can find some attributes (fields) which could have assigned multiply values. One of this kind of filed is userCertificate. In that case I don't know how I can proceed this kind of field to get the values of it? Is there any builtin method? Below you can find part of code which I am using to work with data:

Code:
Dim oRoot
    Set oRoot = GetObject("LDAP://rootDSE")
    Dim sDomain
    sDomain = oRoot.Get("defaultNamingContext")
    
    Dim strLDAP
    strLDAP = "LDAP://" & sDomain
    
    Cells.Select
    Selection.ClearContents
   
    
    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") = 100
    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
    
    objCommand.CommandText = "SELECT adsPath FROM '" & strLDAP & "' WHERE objectCategory='person'AND objectClass='user'"
    Set objRecordSet = objCommand.Execute

.
.
.

Do Until objRecordSet.EOF
            Set oUser = GetObject(objRecordSet.Fields("aDSPath"))
            skip = oUser.sAMAccountName
            disa = oUser.AccountDisabled
                        
            If (skip = "Administrator") Or (skip = "Guest") Or (skip = "krbtgt") Or (disa = "True") Then
                .Cells(x, 1).Value = "test"
                DoEvents
                objRecordSet.MoveNext
            Else
                .Cells(x, 8).NumberFormat = "General"
                .Cells(x, 1).Value = x - 1
                .Cells(x, 2).Value = oUser.sn & " " & oUser.givenName
                .Cells(x, 3).Value = oUser.department
                .Cells(x, 4).Value = oUser.l
               [SIZE=3] [COLOR=#ff0000].Cells(x, 5).Value = oUser.userCertificate[/COLOR][/SIZE]

How I can get this value. How I can work on it? May I convert it somehow to String? Any other possibilities?

Thank you in advance,
Best Regards,
sseba15
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,214,951
Messages
6,122,442
Members
449,083
Latest member
Ava19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top