Merge multiple XLSX files into one

Welcome Forums General PowerShell Q&A Merge multiple XLSX files into one

This topic contains 0 replies, has 1 voice, and was last updated by  nintendroid64 1 month ago.

  • Author
    Posts
  • #172069

    Participant
    Topics: 1
    Replies: 0
    Points: 1
    Rank: Member

    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()

You must be logged in to reply to this topic.