Powershell script to read from multiple excel files in a folder and copy to master file

All replies

• 

  

  0

  Sign in to vote

  Please read this first: This forum is for scripting questions rather than script requests  
  Also find scripts here: http://gallery.technet.microsoft.com

  ---

  \_(ツ)_/

  Sunday, September 3, 2017 8:27 AM
• 

  

  1

  Sign in to vote

  Hi R V Prasad,
  
  Based on my research, if you need to merge multiple Excel files into one, you could try to use the following scripts, this example merged Book1’s sheet1, Book1’s sheet2 and Book2’s sheet1 into Book3, for your reference:
  

  $file1 = 'D:\Book1.xlsx' # source's fullpath
  $file2 = 'D:\Book2.xlsx' # source's fullpath
  $file3 = 'D:\Book3.xlsx' # destination's fullpath
  $xl = new-object -c excel.application
  $xl.displayAlerts = $false # don't prompt the user
  $wb1 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
  $wb2 = $xl.workbooks.open($file2, $null, $true) # open source, readonly
  $wb3 = $xl.workbooks.open($file3) # open target
  $sh1_wb3 = $wb3.sheets.item(1) # first sheet in destination workbook
  $sheetToCopy = $wb1.sheets.item('sheet1') # source sheet to copy
  $sheetToCopy.copy($sh1_wb3) # copy source sheet to destination workbook
  $sheetToCopy = $wb1.sheets.item('sheet2') # source sheet to copy
  $sheetToCopy.copy($sh1_wb3) # copy source sheet to destination workbook
  $sheetToCopy = $wb2.sheets.item('sheet1') # source sheet to copy
  $sheetToCopy.copy($sh1_wb3) # copy source sheet to destination workbook
  $wb1.close($false) # close source workbook w/o saving
  $wb2.close($false) # close source workbook w/o saving
  $wb3.close($true) # close and save destination workbook
  $xl.quit()
  spps -n excel 

  
  If you need further help, please feel free to let us know.
  
  Best Regards,
  Albert Ling

  ---

  Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
  If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

  • Marked as answer by R V Prasad Wednesday, September 6, 2017 5:02 AM

  Monday, September 4, 2017 2:22 AM
• 

  

  1

  Sign in to vote

  The only item that may need explainng is the Destination Workbook - it needs to exist - here are a couple of samples:

  ' Create Blank Workbook in VBScript
  
  Set objShell = WScript.CreateObject("WScript.Shell")
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = False
  Set objWorkbook = objExcel.Workbooks.Add(1)
  Set objWorkSheet = objWorkbook.Sheets(1)
  objWorkbook.SaveAs "C:\Work\MyWorkBook.xlsx"
  objExcel.DisplayAlerts = False
  objWorkbook.Close  False
  objExcel.Quit
  Set objWorksheet = Nothing
  Set objWorkbook  = Nothing
  Set objExcel     = Nothing
  'objShell.Run "TASKKILL /IM excel.exe",1,True   'Optional - May be Needed
  Set objShell = Nothing
  
  
  ****************************************************
  
  
  # Create Blank Workbook in Powershell
  
  $excel = New-Object -ComObject excel.application 
  $excel.visible = $False
  $workbook = $excel.Workbooks.Add()
  $workbook.SaveAs("c:\work\MyWorkbook.xlsx") 
  $excel.Quit()
  
  
  ****************************************************
  
  # Append Three Existing Workbooks into One Workbook
  # Each in its own Worksheet
  # Add WorkBooks and Respective Worksheet Names as Needed
  
  $file1 = 'C:\Users\Data1.xlsx' # source's fullpath
  $file2 = 'C:\Users\Data2.xlsx' # source's fullpath
  $file3 = 'C:\Users\Data3.xlsx' # source's fullpath
  $file4 = 'C:\Work\DataOut.xlsx' # destination's fullpath
  $xl = new-object -c excel.application
  $xl.displayAlerts = $false # don't prompt the user
  $wb1 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
  $wb2 = $xl.workbooks.open($file2, $null, $true) # open source, readonly
  $wb3 = $xl.workbooks.open($file3, $null, $true) # open source, readonly
  $wb4 = $xl.workbooks.open($file4) # open target
  $sh1_wb4 = $wb4.sheets.item(1) # first sheet in destination workbook
  $sheetToCopy = $wb1.sheets.item('ActualSheetName') # source sheet to copy
  $sheetToCopy.copy($sh1_wb4) # copy source sheet to destination workbook
  $sheetToCopy = $wb2.sheets.item('ActualSheetName') # source sheet to copy
  $sheetToCopy.copy($sh1_wb4) # copy source sheet to destination workbook
  $sheetToCopy = $wb3.sheets.item('ActualSheetName') # source sheet to copy
  $sheetToCopy.copy($sh1_wb4) # copy source sheet to destination workbook
  $wb1.close($false) # close source workbook w/o saving
  $wb2.close($false) # close source workbook w/o saving
  $wb3.close($false) # close source workbook w/o saving
  $wb4.close($true) # close and save destination workbook
  $xl.quit()
  spps -n excel # Kill Excel Process
  

  Tuesday, November 7, 2017 2:54 PM
• 

  

  0

  Sign in to vote

  The only item that may need explainng is the Destination Workbook - it needs to exist - here are a couple of samples:

  
  

  The item marked as the answer already does this. Please read the topic more carefully.

  ---

  \_(ツ)_/

  Tuesday, November 7, 2017 2:59 PM