Check Worksheet that has same names from Multiple Workbooks using Powershell

Question

I don't know how to explain this, but I'm trying to create a Comparison Tool using Powershell. I have Multiple Source Files with multiple worksheets that has same names with the other Source files.

My problem is how to get the contents of every worksheet under the same worksheet names and paste it in the Output File and generate a new Worksheet with a same name as the Source Worksheets.

So far I have this, and it works on copying the contents of each worksheets. But I'm having error on worksheet renaming because it's already existing. I already have the idea by putting an "If statement" but it confuses me on what condition to put.

ForEach($File in $Files){
$Source = $Excel.Workbooks.Open($File,$true,$true)
$SourceSheets = $Source.Worksheets

For ($i = 1; $i -le $SourceSheets.Count; $i++){
    # Select the current sheet of the Source
    $currentSheet = $Source.Sheets($i)
    # Copy the desired Range
    $currentSheet.Range("L6","M$(($currentSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()

    # Add a new worksheet in Output and Rename it same as with the Source sheets
    $OutputSheet = $Dest.Worksheets.Add()
    $OutputSheet.Name = $Source.Sheets.Item($i).Name

    If ($OutputSheet.Name is already existing){
        Paste the Copied Contents from Source and Paste it in OutputSheet
    } Else {
        Add new Worksheet
    }
    
    $Dest.SaveAs("$ParentFolder\Output Folder\SampleOutput.xlsx")
}

Worksheet in Source Files:

Sample Template of Output File:

I'm having fun learning this so Thank you for taking time for this problem :)

Answer 1

I thought you would like to know how to loop through all sheets in a book.

# $source = $excel.Workbooks.Open($File)
#Loop through sheets
for ($i = 1; $i -le $source.Sheets.Count; $i++){
    $currentSheet = $source.Sheets($i)
    #Do something on $currentSheet
}

For the second question, I would do it like below.

# make a list for file you would like to look up
$files = @("File1.xlsx","File2.xlsx","File3.xlsx")
# loop through $files
for ($j = 0; $j -lt $files.Count; $j++){
    $FullPath = (Get-ChildItem -Path $files[$j]).FullName
    $source = $excel.Workbooks.Open($FullPath)
    #Do something on $source
}

I hope this helps.

#20-09-2022 add

For the another question, I would get the names of all sheets first.

# before the loop
$arraySheetNames = @()
# get all names
# if $outputFile not exists (make it later), you do not need following for-loop
for ($i = 1; $i -le $outputFile.Count; $i++) {
    $arraySheetNames += $outputFile.Sheets($i).Name
}

# in the loop
# check if the sheet already exists
if (-not $arraySheetNames.Contains($currentSheet.Name)) {
    # if the same name doesn't exist, add sheets and put 
    $OutputSheet = $Dest.Worksheets.Add()
    $OutputSheet.Name = $Source.Sheets.Item($i).Name
    $arraySheetNames += $OutputSheet.Name
}