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
}
}
# 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
}
}
