Import multiple CSV files into separate Excel worksheets

 #import multiple CSV files into separate Excel worksheets
$wrkfldr='C:\temp'

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$wb = $excel.Workbooks.Add()

Get-ChildItem $wrkfldr\*.csv | ForEach-Object {
    if ((Import-Csv $_.FullName).Length -gt 0) {
        $csvBook = $excel.Workbooks.Open($_.FullName)
        $csvBook.ActiveSheet.Copy($wb.Worksheets($wb.Worksheets.Count))
        $csvBook.Close()
    }
}

List Members of AD groups

To get the members of a group, we need to login into a server with an admin account.

The admin account is member of another domain in the same forest, but the groups are in a different domain. In order to perform the inquiry, an AD controller server for the target domain must be specified.

Simple select: Get-ADGroup -Filter { Name -like "*the_searched_group*" } -Server DC.TARGET.TLD | Get-ADGroupMember -Server DC.TARGET.TLD | Select-Object name, objectClass | Out-GridView

#to be run as normal user, so excel lauches without interference, it will ask for admin cred when needed

$wrkfldr='C:\temp'
$server='DC.TARGET.TLD'
$grps="app*-VNC*"
$cred = Get-Credential;
$ErrorActionPreference= 'silentlycontinue'

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$wb = $excel.Workbooks.Add()

$groups = Get-ADGroup -Credential $cred -filter { name -like $grps } -server $server | Select Name -ExpandProperty Name

foreach ($group in $groups){ Get-ADGroupMember -Credential $cred -identity $group -server $server | Where-Object {$_.objectClass -eq "user"} | Select-Object Name | Export-Csv "$wrkfldr\$group.csv" -NoTypeInformation }

Get-ChildItem $wrkfldr\*.csv | ForEach-Object {
if ((Import-Csv $_.FullName).Length -gt 0) {
    $csvBook = $excel.Workbooks.Open($_.FullName)
    $csvBook.ActiveSheet.Copy($wb.Worksheets($wb.Worksheets.Count))
    $csvBook.Close()
    }
}