VBA 繼承 VB6 傳統,可引用 ADO、ADSI 等 COM 元件,直接在 Excel/Word 中操作 Active Directory。本文示範在 Excel 按鈕點擊事件中,以 ADO 連接 AD (LDAP),查詢所有用戶帳號並寫入工作表,展現 VBA 作為系統管理腳本的強大威力。
dc=corp,dc=example,dc=com
Option Explicit
Private Sub CommandButton1_Click()
On Error GoTo ErrHandler
Const ADS_SCOPE_SUBTREE = 2
Dim objConnection As Object, objCommand As Object, objRecordSet As Object
Dim currCell As Range
Dim domainDN As String
' 修改為您的網域 DN
domainDN = "dc=XXX,dc=com"
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
' 分頁大小 (避免 AD 預設 1000 筆限制)
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
' LDAP 查詢:所有用戶物件
objCommand.CommandText = _
"SELECT Name, sAMAccountName, mail, department, telephoneNumber " & _
"FROM 'LDAP://" & domainDN & "' " & _
"WHERE objectCategory='person' AND objectClass='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
' 寫入標題列
Set currCell = Range("A1")
currCell.Resize(1, 5).Value = Array("顯示名稱", "登入帳號", "郵箱", "部門", "電話")
currCell.Resize(1, 5).Font.Bold = True
' 迴圈寫入資料
Do Until objRecordSet.EOF
Set currCell = currCell.Offset(1, 0)
currCell.Resize(1, 5).Value = Array( _
objRecordSet.Fields("Name").Value, _
objRecordSet.Fields("sAMAccountName").Value, _
objRecordSet.Fields("mail").Value, _
objRecordSet.Fields("department").Value, _
objRecordSet.Fields("telephoneNumber").Value _
)
objRecordSet.MoveNext
Loop
' 自動調整欄寬
Columns("A:E").AutoFit
MsgBox "查詢完成,共 " & objRecordSet.RecordCount & " 筆。", vbInformation
Exit Sub
ErrHandler:
MsgBox "錯誤 " & Err.Number & ":" & Err.Description, vbCritical
On Error GoTo 0
End Sub
| 項目 | 說明 |
|---|---|
| Provider | ADsDSOObject (Active Directory Service Interfaces OLE DB Provider) |
| Connection String | "Active Directory Provider" 固定字串,以目前安全性內文連接 |
| Page Size | 設為 1000 突破 AD 預設 1000 筆回傳限制 (需 AD 支援分頁) |
| Searchscope | ADS_SCOPE_SUBTREE=2 遞迴搜尋整棵樹 |
| LDAP Filter | objectCategory='person' AND objectClass='user' 精準鎖定用戶帳號 |
| 欄位名稱 | 使用 LDAP 屬性名:Name, sAMAccountName, mail, department, telephoneNumber 等 |
cn / Name 顯示名稱
sAMAccountName 登入帳號 (pre-Windows 2000)
userPrincipalName UPN (user@domain.com)
mail 電子郵件
department 部門
title 職稱
telephoneNumber 辦公電話
mobile 手機
physicalDeliveryOfficeName 辦公室位置
manager 主管 DN
memberOf 所屬群組 DN 陣列
whenCreated 建立時間
whenChanged 最後修改時間
lastLogon 最後登入時間 (單一 DC)
lastLogonTimestamp 最後登入時間 (複寫延遲)
GetObject("LDAP://CN=..., " & domainDN) 取得 IADsUser 介面直接 Put/SetInfoCreateObject("LDAP://...") 建立/更新用戶memberOf、操作 group 物件的 Add/Remove 方法LDAP://gc.corp.example.com/... 支援跨域搜尋Page Size 並使用 MoveNext 迴圈,勿一次 GetRows 導致記憶體飆升
3. 機密屬性 (如 userPassword) 預設不可讀,需特殊權限
4. 釋放物件:Set objRecordSet = Nothing 等,防止 COM 參考計數洩漏
VBA + ADO + ADSI = 無需安裝額外工具的輕量級 AD 管理利器。善用 Excel 表格化呈現、篩選、圖表化,讓 IT 管理員能快速產出用戶清單、稽核報表、離職帳號檢核等實務產出。掌握 LDAP 查詢語法與常用屬性名稱,即可舉一反三開發各類 AD 自動化腳本。