title: Save an open Excel sheet using Powershell
body: <p>I am completely newbie to Powershell. Need your help in saving an opened excel sheet using Powershell.  </p>
  
  <p>Script goes something like this</p>
  
  <pre><code>$xlPasteValues = -4163
  $xlCellTypeLastCell = 11
  $xl = new-object -comobject excel.application
  $xl.Visible = $True
  $xl.DisplayAlerts = $False
  $wb = $xl.Workbooks.Add()
  $i = 1
  $collection = Get-ChildItem C:\Test\* -include *.csv  # Change the location of your CSV files here.
  $length = 4
  foreach ($item in $collection) {
      $wb1 = $xl.Workbooks.Open("$item")
      $array = $item.ToString()
      $delim = "\"
      $SheetName = $array.split($delim)
      $s = $SheetName[2]
      $sn = $s.split(".")
      $nsn = $sn[0]
      $ws1 = $wb1.worksheets | where {$_.name -eq $nsn}
  Write-Host $item $nsn
  $used = $ws1.usedRange
  $used.Select()
  $used.copy()
  $wb.Activate()
  $ws = $wb.Sheets.Add()
  $ws2 = $wb.worksheets | where {$_.name -eq "sheet$i"}
  [void]$ws2.Range("A1").PasteSpecial(-4163)
  $ws2.name = $nsn
  $i++ 
  $wb1.Close()
  }
  Add-Type -AssemblyName Microsoft.Office.Interop.Excel
  $xlFixedFormat =[Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
  $Excel = New-Object -comobject Excel.Application
  $Excel.Visible = $true
  </code></pre>