title: Merge content of multiple Excel files into one using PowerShell
body: <p>I have multiple Excel files with different names in path. </p>
  
  <p>e.g. <code>C:\Users\XXXX\Downloads\report</code></p>
  
  <p>Each file has a fixed number of columns.</p>
  
  <p>e.g. <code>Date | Downtime | Response</code></p>
  
  <p>I want to create a new Excel file with merge of all Excel data. New column should be added with client name in which i want to enter file name. Then each Excel file data append below one by one.</p>
  
  <p>e.g. <code>Client name | Date | Downtime | Response</code></p>
  
  <p>Below code can able to append all excel data but now need to add Client name column.</p>
  
  <pre><code>$path = "C:\Users\XXXX\Downloads\report"
  
  #Launch Excel, and make it do as its told (supress confirmations)
  $Excel = New-Object -ComObject Excel.Application
  $Excel.Visible = $True
  $Excel.DisplayAlerts = $False
  $Files = Get-ChildItem -Path $path
  
  #Open up a new workbook
  $Dest = $Excel.Workbooks.Add()
  
  #Loop through files, opening each, selecting the Used range, and only grabbing the first 5 columns of it. Then find next available row on the destination worksheet and paste the data
  ForEach($File in $Files)
  {
      $Source = $Excel.Workbooks.Open($File.FullName,$true,$true)
      If(($Dest.ActiveSheet.UsedRange.Count -eq 1) -and ([String]::IsNullOrEmpty($Dest.ActiveSheet.Range("A1").Value2)))
      { 
          #If there is only 1 used cell and it is blank select A1
          [void]$source.ActiveSheet.Range("A1","E$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
          [void]$Dest.Activate()
          [void]$Dest.ActiveSheet.Range("A1").Select()
      }
      Else
      { 
          #If there is data go to the next empty row and select Column A
          [void]$source.ActiveSheet.Range("A2","E$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
          [void]$Dest.Activate()
          [void]$Dest.ActiveSheet.Range("A$(($Dest.ActiveSheet.UsedRange.Rows|Select -last 1).row+1)").Select()
      }
      [void]$Dest.ActiveSheet.Paste()
      $Source.Close()
  }
  $Dest.SaveAs("$path\Merge.xls")
  $Dest.close()
  $Excel.Quit()
  </code></pre>
  
  <p>Suggest any effective way to do this. Please provide links if available.</p>
  
  <p>Convert XLS to XLSX :</p>
  
  <pre><code>$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
  $excel = New-Object -ComObject excel.application
  $excel.visible = $true
  $folderpath = "C:\Users\xxxx\Downloads\report\*"
  $filetype ="*xls"
  Get-ChildItem -Path $folderpath -Include $filetype | 
  ForEach-Object `
  {
      $path = ($_.fullname).substring(0,($_.FullName).lastindexOf("."))
      "Converting $path to $filetype..."
      $workbook = $excel.workbooks.open($_.fullname)
  
      $workbook.saveas($path, $xlFixedFormat)
      $workbook.close()
  }
  $excel.Quit()
  $excel = $null
  [gc]::collect()
  [gc]::WaitForPendingFinalizers()
  </code></pre>