PowerShell - Enumerating groups to Excel

A friend had a semi-regular request for the members of a long list of distribution groups (70-80). I hacked together something that dumped everthing to a big text file which he then had to convert to an Excel spreadsheet. Not an optimal solution for automation. I thought this was an excellent time to learn how to put data in Excel.

The script as written takes input from a text file of the group (Distribution lists, but any group would work) but you could change that to a query easily enough.

Some issues I ran into;

  • Quest AD tools (Get-QADGroup) - trying to get a group that has multiple posible matches has an error unless you use the -DisplayName switch.
  • Excel tab names can only be 31 characters long. I now test for that. We do not have any of the illegal characters in our group names so I do not test for that (see comments in code) but would be easy enough for someone to add.

Here it is, feedback welcome.

# ==========================================================================
#    NAME: enumerate-DistributionListsToExcel.ps1
#  AUTHOR: Steven Peck
#    DATE: 6/25/2008
# COMMENT: 
# ==========================================================================
 
# Source of original list - $list could be used to a number of ways
$list = Get-Content "C:\scripts\distributionlists.txt"
 
# Create an Excel object
$Excel = New-Object -Com Excel.Application
 
# Sets to visible, for production set to $False
$Excel.visible = $True
 
# Get the number of lists to use as the sheet count for the workbook
$Excel.SheetsInNewWorkbook = $list.Count
# Create the workbook
$wb = $Excel.Workbooks.Add()
 
# First loop to rename and populate tabs
$list | ForEach-Object {$i = 1} {
  $sheet = $wb.Sheets.Item($i++)
  # Test for name length (Excel max length of 31 characters in tab)
  # Excel has character limitations not tested for in this script
  #   The following characters are invalid names:  :  \  /  ?  *  [  or  ]
  if ( $_.length -gt 30 ) {
    $sheet.name = $_.substring(0,30)           # Truncates names that are to long 
    }
    else {
    $sheet.name = $_                           # names it the current distribution list name
    }
  $Sheet.Cells.Item(1,1) = $_                  # cell 1,1 with distribution list name
  $Sheet.Cells.Item(2,1) = "Display Name"      # header row 
  $Sheet.Cells.Item(2,2) = "Type"              # header row
  $WorkBook = $Sheet.UsedRange                 # determine existing range with content '
  $WorkBook.Font.Bold = $True                  # and make it bold
 
  $intRow = 3                                  # set a variable to 3 for the later row count
  # Get the list members, sort by type then displayname
  $listMembers = Get-QADGroupMember (Get-QADGroup -DisplayName $_) -Indirect -SizeLimit 0 | Sort-Object type, displayname
  # Nested look to populate the list members on the current page
  foreach ($member in $listMembers) {
    $Sheet.Cells.Item($intRow,1) = $member.DisplayName  # use DisplayName property column 1
    $Sheet.Cells.Item($intRow,2) = $member.Type         # use Type property column 2
 
    $intRow = $intRow + 1                               # increment to next row
    }
  }

Commenting on this Blog entry is closed.