Facing issues while using ImportExcel PowerShell module

[Ashish_Arya]

Hi All,

I am trying to export Azure AD group member details to a single excel workbook where every group member details should be mentioned in a separate worksheet. For example, group1 worksheet should contains members of one AAD group and group2 worksheet contains members of another AAD group and so on so forth.

For this I am using two PowerShell modules: Microsoft.Graph and ImportExcel.

The issue is that once 34 worksheets are added to the excel workbook, I get this error :

InvocationInfo    : 
  MyCommand    : Add-ExcelTable
  ScriptLineNumber : 428
  OffsetInLine   : 39
  HistoryId    : 122
  ScriptName    : C:\Users\ashish\Documents\PowerShell\Modules\ImportExcel\7.8.4\Public\Export-Excel.ps1
  Line       : Add-ExcelTable -Range $ws.Cells[$dataRange] -TableName $TableName -TableStyle $TableStyle -TableTotalSettings $TableTotalSettings
            
  PositionMessage : At C:\Users\ashish\Documents\PowerShell\Modules\ImportExcel\7.8.4\Public\Export-Excel.ps1:428 char:39
            +         Add-ExcelTable -Range $ws.Cells[$dataRange] -TableNam …
            +                    ~~~~~~~~~~~~~~~~~~~~~

===============================

Below is my code for reference.

param(

    [Parameter(Mandatory,

        HelpMessage = "Enter the pattern for filtering groups"

    )]

    $Pattern

)

# Excel file path 

$ExcelFilePath = "$($PSScriptroot)\GroupMembers.xlsx"

# Azure AD App details

$ApplicationId = $Env:Azure_CLIENT_ID

$TenantID = $Env:Azure_TENANT_ID

$ClientSecret = $Env:Azure_CLIENT_SECRET | ConvertTo-SecureString -AsPlainText -Force

$ClientSecretCredential = New-Object -TypeName 'System.Management.Automation.PSCredential' -ArgumentList $ApplicationId, $ClientSecret

# Connecting to Microsoft Graph

Connect-MgGraph -TenantId $TenantID -ClientSecretCredential $ClientSecretCredential | Out-Null

# Getting all the groups with displayname starting with the provided pattern

$Groups = Get-MgGroup -filter "startswith(displayname,'$Pattern')" -Top 2000

# Looping through all the filtered groups and exporting their members to the csv files

$Count = 0

foreach ($Group in $Groups) {

    $Count += 1

    $WorkSheetName = "Group$($Count)" 

    Try{

    (Get-MgGroupMember -GroupId $Group.id -Top 150).AdditionalProperties | `

            Select-Object @{n = "DisplayName"; e = { $_.displayName } }, @{n = "UserprincipalName"; e = { $_.userPrincipalName } } |`

            Export-Excel -path $ExcelFilePath -WorksheetName $WorkSheetName -Append -TableStyle 'Medium16' `

            -Title $Group.Displayname -TitleSize 14 -TitleBold 

    }

    Catch {

        Write-Host $_.Exception.Message -ForegroundColor 'Red'

        Break

    }

}

Any help would be appreciated.

Regards,

Ashish Arya

[#2]

@Ashish_Arya 

The error message indicates that the maximum number of worksheets in an Excel workbook, 255, has been reached. One solution is creating multiple workbooks, each containing fewer than or equal to 255 worksheets. I modified your code so that it creates a new workbook when the number of worksheets reaches 255 and then continues adding worksheets to the next workbook; here's an example implementation:

param(

    [Parameter(Mandatory,

        HelpMessage = "Enter the pattern for filtering groups"

    )]

    $Pattern

)

# Excel file path 
$ExcelFilePath = "$($PSScriptroot)\GroupMembers"
$WorkbookNumber = 0
$WorksheetCount = 0

# Azure AD App details
$ApplicationId = $Env:Azure_CLIENT_ID
$TenantID = $Env:Azure_TENANT_ID
$ClientSecret = $Env:Azure_CLIENT_SECRET | ConvertTo-SecureString -AsPlainText -Force
$ClientSecretCredential = New-Object -TypeName 'System.Management.Automation.PSCredential' -ArgumentList $ApplicationId, $ClientSecret

# Connecting to Microsoft Graph
Connect-MgGraph -TenantId $TenantID -ClientSecretCredential $ClientSecretCredential | Out-Null

# Getting all the groups with displayname starting with the provided pattern
$Groups = Get-MgGroup -filter "startswith(displayname,'$Pattern')" -Top 2000

# Looping through all the filtered groups and exporting their members to the csv files
foreach ($Group in $Groups) {
    $WorksheetCount += 1

    if ($WorksheetCount -eq 256) {
        $WorkbookNumber += 1
        $WorksheetCount = 1
    }

    $WorkSheetName = "Group$($WorksheetCount)" 
    $ExcelFile = "$ExcelFilePath$($WorkbookNumber).xlsx"

    Try{
        (Get-MgGroupMember -GroupId $Group.id -Top 150).AdditionalProperties | `
            Select-Object @{n = "DisplayName"; e = { $_.displayName } }, @{n = "UserprincipalName"; e = { $_.userPrincipalName } } |`
            Export-Excel -path $ExcelFile -WorksheetName $WorkSheetName -Append -TableStyle 'Medium16' `
            -Title $Group.Displayname -TitleSize 14 -TitleBold 
    }

    Catch {
        Write-Host $_.Exception.Message -ForegroundColor 'Red'
        Break
    }
}