Create an Excel report of Active Directory Users in an OU

ldap_user_rpt.ps1

# A script to fetch AD Users and put them into a spreadsheet
# Dan Wanek <[email protected]>
# 08/24/2011 22:36:00

param(
  [int]$limit      = 20,
  [string]$ou      = "",
  [string]$outFile = $(Get-Location).Path + "\LDAP_USER_RPT.xls",
  [switch]$verbose,
  [switch]$usage
)

Import-Module ActiveDirectory

function usage {
  Clear-Host
  Write-Host "-----------------------------------------------------------------------------------------------"
  Write-Host "-ou <ou>          : The full path to the OU you want to search from"
  Write-Host "-outFile <of> : Defaults to the current directory\LDAP_USER_RPT.xls"
  Write-Host "-verbose          : If this flag is passed you will see the Excel spreadsheet as it's filled out"
  Write-Host "-usage            : This message"
  Write-Host "-----------------------------------------------------------------------------------------------"
  Write-Host "Example:"
  Write-Host "    ldap_report.ps1 -limit 20 -ou 'ou=myou,dc=example,dc=com' -verbose"
  Write-Host "-----------------------------------------------------------------------------------------------"
  Exit
}

function getADUsers {
  $ad_users = Get-ADUser -LDAPFilter '(objectClass=person)' -SearchBase "$ou" -ResultSetSize $limit
  return $ad_users
}

function writeExcel($adUsers) {
  $excel = New-Object -comobject excel.application
  if($verbose) {
    $excel.Visible = $true
  }

  # Create a Workbook
  $wb1 = $excel.Workbooks.Add()
  # Get access to Worksheet 1
  $ws1 = $wb1.Worksheets.Item(1)

  $ws1.Cells.Item(1,1) = "Name"
  $ws1.Cells.Item(1,2) = "AD User ID"
  $range = $ws1.UsedRange
  $range.Font.Bold = $true
  $range.Interior.ColorIndex = 6
  for($i=0; $i -lt $adUsers.Length; $i++) {
    Write-Host -n '='
    $ws1.Cells.Item($i+2,1) = $adUsers[$i].Name
    $ws1.Cells.Item($i+2,2) = $adUsers[$i].samaccountname
  }
  Write-Host ""
  [Void]$ws1.Columns.Item(1).EntireColumn.AutoFit()
  [Void]$ws1.Columns.Item(2).EntireColumn.AutoFit()

  $ver = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlExcel8
  $wb1.SaveAs($outFile, $ver)
  $excel.Quit()
}

if($usage) {
  usage
} else {
  $ad_users = getADUsers

  if (Test-Path $outFile) {
    Write-Host "Removing existing report...."
    Remove-Item $outFile
  }
  writeExcel($ad_users)
}