萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> Java

VBA 操作 Active Directory (AD) 完整教學:用 ADO 查詢域用戶並寫入 Excel

VBA 繼承 VB6 傳統,可引用 ADO、ADSI 等 COM 元件,直接在 Excel/Word 中操作 Active Directory。本文示範在 Excel 按鈕點擊事件中,以 ADO 連接 AD (LDAP),查詢所有用戶帳號並寫入工作表,展現 VBA 作為系統管理腳本的強大威力。

前置準備

  1. Excel 啟用 Microsoft ActiveX Data Objects x.x Library 參考 (工具 → 參考)
  2. 2. 執行權限:需具備讀取 AD 的網域用戶身分 (通常以目前登入身分執行) 3. 知悉網域 DN (Distinguished Name),如 dc=corp,dc=example,dc=com

完整 VBA 程式碼 (標準模組或工作表物件)

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

關鍵點解析

項目說明
ProviderADsDSOObject (Active Directory Service Interfaces OLE DB Provider)
Connection String"Active Directory Provider" 固定字串,以目前安全性內文連接
Page Size設為 1000 突破 AD 預設 1000 筆回傳限制 (需 AD 支援分頁)
SearchscopeADS_SCOPE_SUBTREE=2 遞迴搜尋整棵樹
LDAP FilterobjectCategory='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/SetInfo
  • 同步 Excel → AD:讀取工作表資料,逐筆 CreateObject("LDAP://...") 建立/更新用戶
  • 群組成員管理:查詢 memberOf、操作 group 物件的 Add/Remove 方法
  • 跨域查詢:指定 GC (Global Catalog) LDAP://gc.corp.example.com/... 支援跨域搜尋

安全與效能提醒

  1. 避免在生產環境直接測試大量寫入操作,先在測試網域驗證
  2. 2. 大量查詢時設定 Page Size 並使用 MoveNext 迴圈,勿一次 GetRows 導致記憶體飆升 3. 機密屬性 (如 userPassword) 預設不可讀,需特殊權限 4. 釋放物件:Set objRecordSet = Nothing 等,防止 COM 參考計數洩漏

小結

VBA + ADO + ADSI = 無需安裝額外工具的輕量級 AD 管理利器。善用 Excel 表格化呈現、篩選、圖表化,讓 IT 管理員能快速產出用戶清單、稽核報表、離職帳號檢核等實務產出。掌握 LDAP 查詢語法與常用屬性名稱,即可舉一反三開發各類 AD 自動化腳本。

copyright © 萬盛學電腦網 all rights reserved