Retrieving users in AD using VBA

As a scripting language used in Microsoft Office Products, VBA is actually very powerful, thanks to its inheritance of many excellent Visual Basic traditions, for example, we can introduce a class library or ActiveX control in the VBA project like VB 6. Of course, the components or controls you use in VB 6 can also be used in VBA, such as The Date input component.

VBA supports ado, which means that we can operate databases in VBA, including SQL Server and access. Because of this, you can try to write a VBA in ExcelProgramExcel is used as a middleware for data synchronization. This middleware can be used to synchronize the data in the application and the background database, and perform some processing in it. This is exactly what we can do! I have done this in a previous project. We even used EXCEL to synchronize data between SQL Server and Sharepoint list (I will introduce it later ).

ADO is also very powerful. We can execute windows scripts in it. We often use it to manage Windows Server's ad, such as traversing accounts in AD, it's no stranger to modifying accounts in the Active Directory in batches. You can use NotePad to write a vbs script on Windows and then use it to manage the Active Directory on your server, of course, security considerations require proper execution permissions. These work can also be done through VBA, and you will find it very convenient in Excel, such as using the existing UI of EXCEL to display the operated data; generate charts based on the queried data, use the functions provided by Excel to modify data, and facilitate data traversal and organization...

The following is an example. Add an ActiveX button to the new sheet, and then add the followingCode.

Private   Sub Commandbutton#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 from the 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

In the code, I used an imaginary domain name named XXX. If you want to test it, you can change it to a real domain name. We use ADO to perform user queries on the specified domain, and output the names of all users in the domain to the first column of the current sheet in Excel in sequence. The following is the output part.

For how to operate ad through scripts, readers can refer to Microsoft's official documentation http://www.microsoft.com/technet/scriptcenter/guide/sas_usr_ykxh.mspx? MFR = true

If the permission permits, you can modify the code above to implement the function of modifying accounts in AD in batches. In view of the company's network environment, it is not convenient to test the function, if you have the conditions, you can try it yourself.