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

Viewing 0 reply threads
  • 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()
Viewing 0 reply threads
  • The topic ‘Merge multiple XLSX files into one’ is closed to new replies.