Powershell to compile contents of same worksheet names and save it with a same worksheet name

Viewed 24

I'm trying 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. The worksheets in Output File are just piling up as Sheet1,Sheet2 and so on after copying the first worksheets... So what I'm trying to do is If the worksheetname is already existing, copy the content of the second File with the same worksheet name but paste it in another cell.

#Open up a new workbook
$Dest = $Excel.Workbooks.Add()

ForEach($File in $Files){
    #Open the Source Workbooks and Worksheets
    $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 of each current Sheets
        $currentSheet.Range("L6","O$(($currentSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
        
        # Call the Worksheets in Ouput
        $DestSheets = $Dest.Worksheets

        ForEach ($OutSheet in $DestSheets) {

            # Get the Worksheets name in Source
            $currentSheetName = $currentSheet.Name

            If ($currentSheetName -match $DestSheets) {

               [void]$DestSheets.Range("A2:E8").PasteSpecial(-4163)
                
            } Else {

                $OutputSheet = $DestSheets.Add()
                $OutputSheet.Name = $currentSheetName
            }
        }

        $Dest.SaveAs("$ParentFolder\Output Folder\Out.xlsx")
    }
}
0 Answers
Related