Merge excel spreadsheets into same worksheet

hi all,

         looking merge approx. 150 xlsx files single worksheet using powershell. (its aprt of larger script performs other functions)

there seem many examples or merging multiple files multiple worksheets - haven't been able of them merge same worksheet.

this ongoing requirement, while exporting csv's, merging , re-opening work - not long term solution im looking for.

hi,

if worksheet different workbook, try below code:

this copies 'sheettocopy' sheet source workbook (book1.xlsx) destination workbook (book2.xlsx).
 
$file1 = 'c:\book1.xlsx'                            # source's fullpath
$file2 = 'c:\book2.xlsx'                       # destination's fullpath
$xl = new-object -c excel.application
$xl.displayalerts = $false                      # don't prompt user
$wb1 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb2 = $xl.workbooks.open($file2)                         # open target
$sh1_wb2 = $wb2.sheets.item(1)    # first sheet in destination workbook
$sheettocopy = $wb1.sheets.item('sheettocopy')   # source sheet copy
$sheettocopy.copy($sh1_wb2) # copy source sheet destination workbook
$wb1.close($false)                   # close source workbook w/o saving
$wb2.close($true)                 # close , save destination workbook
$xl.quit()
spps -n excel

please go through below links, hope helpful:

copy data 1 excel spreadsheet powershell

http://blogs.technet.com/b/heyscriptingguy/archive/2010/09/08/copy-data-from-one-excel-spreadsheet-to-another-with-powershell.aspx

powershell script combine multiple csv files xlsx file

regards,

yan li

---

regards, yan li

Windows Server  >  Windows PowerShell