Merge multiple XLSX files into one

PowerShell Help
1

Hi all,

I’m trying to merge a bunch of XLSX files into one (all have the same number of columns and column headers). All files have columns that are filtered differently, so i only want to copy the filtered data. Is this possible? Code i have so far:

$ExcelObject=New-Object -ComObject excel.application
$ExcelObject.visible=$true
$ExcelFiles=Get-ChildItem -Path C:\Users\filePath...

$Workbook=$ExcelObject.Workbooks.add()
$Worksheet=$Workbook.Sheets.Item("Sheet1")

foreach($ExcelFile in $ExcelFiles){
 
$Everyexcel=$ExcelObject.Workbooks.Open($ExcelFile.FullName)
$Everysheet=$Everyexcel.sheets.item(1)
$Everysheet.Copy($Worksheet)
$Everyexcel.Close()
 
}
$Workbook.SaveAs("C:\Users\filePath...\merge.xlsx")
$ExcelObject.Quit()