Retrieving users in AD through VBA

VBA, as a scripting language used in Microsoft Office products, is actually powerful because it inherits many of the fine traditions of Visual Basic, such as the introduction of class libraries or ActiveX controls in VBA projects like VB 6. Of course, the components or controls you use in VB 6 can also be used in VBA, such as date input components.

VBA supports ADO, which also means that we can manipulate databases in VBA, including SQL Server, Access, and so on. Because of this, you can try writing a VBA program in Excel to use Excel as a middleware for data synchronization, which can be used to synchronize data in the application and back-end databases and do some processing in it. This is totally achievable! As I've done in a previous project, we even synchronized the data between SQL Server and SharePoint list through Excel (which I'll explain later).

ADO is also very powerful, we can execute Windows script, often use it to manage Windows Server's ad, such as traversing the accounts in the ad, the bulk of the account in the ad, etc., which has nothing to be surprised, you can completely Use Notepad to write a VBS script on Windows, and then use it directly to manage the ad on your server, and of course, for security reasons, these require appropriate execution permissions. This work can also be done through VBA, and in Excel you'll find it handy to use Excel's existing UI to show the data you're working on, build the chart based on the data you're querying, modify the data with the features that Excel provides, traverse and organize the convenience of your data, and so on ...

I'll give you an example below. Add an ActiveX Button to the new sheet and add the following code.

Private Sub CommandButton1_Click ()
On Error Resume Next

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 Name from ' ldap://dc=xxx,dc=com ' WHERE objectcategory= ' user '" ' Search All users ' Domn XXX
Set objRecordSet = Objcommand.execute
Objrecordset.movefirst

Dim Currcell as Range
Set Currcell = Range ("A1")

Do Until objrecordset.eof
Currcell.value = Objrecordset.fields ("Name"). Value
Set Currcell = currcell.offset (1, 0)
Objrecordset.movenext
Loop
End Sub