title: How to use powershell to copy several excel worksheets and make a new one?
body: <p>I have about 70 excel files I want to combine into a single document. Each document has only one sheet and follows this format:</p>
  
  <ul>
  <li>Row A with Columns A-F of headings</li>
  <li>Row B with the first entry</li>
  <li>Row C with the second entry</li>
  <li>Up to 150 rows on some sheets</li>
  </ul>
  
  <p>I want to scrape the information from Columns A-F for each row, and combine it into a new file with the information from all of the other files I have in the same directory.</p>
  
  <p>Note: I only want to capture Columns A-F since in Column G there exists a Yes, No dataset to manage the drop down list in Column F.</p>
  
  <p>I tried using dugan's answer from <a href="https://stackoverflow.com/questions/3226096/copy-excel-worksheet-from-one-workbook-to-another-with-powershell/">Copy Excel Worksheet from one Workbook to another with Powershell</a> but it resulted in a file with part of the data spread across two sheets.</p>
  
  <p>Here is that code:</p>
  
  <pre><code>$file1 = 'C:\Users\Matthew.Andress\Documents\Excel Test\Book1.xlsx' # source's fullpath
  $file2 = 'C:\Users\Matthew.Andress\Documents\Excel Test\Book2.xlsx' # destination's fullpath
  $xl = new-object -c excel.application
  $xl.displayAlerts = $false # don't prompt the user
  $wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
  $wb1 = $xl.workbooks.open($file2) # open target
  $sh1_wb1 = $wb1.sheets.item(2) # second sheet in destination workbook
  $sheetToCopy = $wb2.sheets.item('Sheet1') # source sheet to copy
  $sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook
  $wb2.close($false) # close source workbook w/o saving
  $wb1.close($true) # close and save destination workbook
  $xl.quit()
  spps -n excel
  </code></pre>
  
  <p>Any suggestions? Thank you.</p>