Create excel csv files with all computers of each Listed OU

$OUpath = @("OU=example,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=exam.ple,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=example1,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=example2,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=example3,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=example4,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=example5,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=example6,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=example7,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=example8,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=example9,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=CA,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=CAB,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=Ara,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=Gil,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=Kbi,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=Cieux,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=liz,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=mes,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=IO,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=SE,OU=Parent OU,DC=domain name,DC=domain prefix",
  "OU=SC,OU=Parent OU,DC=domain name,DC=domain prefix")

foreach ($distinguishedName in $OUpath)
{
$csvFileName = ($distinguishedName -replace "OU","" -replace "=","" -replace ",","" -replace "Parent","" -replace "DC","" -replace "domain name","" -replace "prefix","") +".csv"


Get-ADComputer -Filter * -Property Name,OperatingSystem,OperatingSystemVersion -SearchBase $i.toString() | Select-Object Name,OperatingSystem,OperatingSystemVersion | Export-CSV $csvFileName -NoTypeInformation -Encoding UTF8
  
}

Function Merge-CSVFiles
{
Param(
$CSVPath = "C:\Users\Haddouch_Fadil\excelSource",
$XLOutput="C:\Users\Haddouch_Fadil\exceloutput\temp.xls" 
)

$csvFiles = Get-ChildItem ("$CSVPath\*") -Include *.csv
$Excel = New-Object -ComObject Excel.Application 
$Excel.visible = $false
$Excel.sheetsInNewWorkbook = $csvFiles.Count
$workbooks = $excel.Workbooks.Add()
$CSVSheet = 1

Foreach ($CSV in $Csvfiles){

$worksheets = $workbooks.worksheets
$CSVFullPath = $CSV.FullName
$SheetName = ($CSV.name -split "\.")[0]
$worksheet = $worksheets.Item($CSVSheet)
$worksheet.Name = $SheetName
$TxtConnector = ("TEXT;" + $CSVFullPath)
$CellRef = $worksheet.Range("A1")
$Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)
$worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True
$worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1
$worksheet.QueryTables.item($Connector.name).Refresh()
$worksheet.QueryTables.item($Connector.name).delete()
$worksheet.UsedRange.EntireColumn.AutoFit()
$CSVSheet++

}

$workbooks.SaveAs($XLOutput,51)
$workbooks.Saved = $true
$workbooks.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbooks) | Out-Null
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

}

Merge-CSVFiles -CSVPath C:\Users\Haddouch_Fadil\excelSource -XLOutput C:\Users\Haddouch_Fadil\exceloutput\temp.xls

Remove-Item * -Include *.csv

This is my final code.

• The script returns all the computer objects inside a OU. It searches with the distinguished name of the OU's placed in the list.

• Generating .csv files with this information for each OU; with the correct output for the file names. (Deleted all the repetitive stuff in the distinguished name of the OU's) This was important in order to create the worksheets with the right name. Otherwise I had to do it manually.

• Convert all csv files into tables and put them in different worksheets in 1 excel file with the call of the Merge-CSVFiles function.

-Deletes generated csv files at the end in the current PowerShell directory

-> My next step is to transform the tables in the worksheets into data tables in Excel with a VB script.