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

  • Question

  • Hi all,

    I'm new to Powershell scripting. I'm creating a powershell to read the data from the each excel file in a given folder and copy then paste in a master excel file in the same folder.

    But it is not working as expected and sometimes it over writing the data. Could one help me on this requirement.

    Thanks in advance
    Sunday, September 3, 2017 8:18 AM

Answers

  • 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

All replies

  • Sunday, September 3, 2017 8:27 AM
  • 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
  • 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
  • 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