locked
Moving multiple Excel Sheets from one Book to another RRS feed

  • Question

  • I am able to get the first 11 sheets to copy from seperate books, but on the 12th sheet on it does not work.  The renaming of the sheets does not work as well.  Does anyone have any suggestions?

    ###########################################################################
    $file1 = 'Test.xls' # source's fullpath 
    $file3 = 'Test.xls' # source's fullpath
    $file4 = 'Test.xls' # source's fullpath
    $file5 = 'Test.xls' # source's fullpath
    $file2 =  'Test.xls'# 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
    $wb3 = $xl.workbooks.open($file3, $null, $true) # open source, readonly
    $wb4 = $xl.workbooks.open($file4, $null, $true) # open source, readonly
    $wb5 = $xl.workbooks.open($file5, $null, $true) # open source, readonly
    $wb1 = $xl.workbooks.open($file2) # open target 

    ###########################################################################
                    ####Copy MSP03 CRA, HOSP, FMP, MCH####
    $sh1_wb1 = $wb1.sheets.item(1) # first sheet in destination workbook 
    $sheetToCopy = $wb2.sheets.item('CRA') # source sheet to copy
    $sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook

    $sh2_wb1 = $wb1.sheets.item(2) # second sheet in destination workbook
    $sheetToCopy = $wb2.sheets.item('HOSP') # source sheet to copy
    $sheetToCopy.copy($sh2_wb1) # copy source sheet to destination workbook 

    $sh3_wb1 = $wb1.sheets.item(3) # third sheet in destination workbook 
    $sheetToCopy = $wb2.sheets.item('FMP') # source sheet to copy 
    $sheetToCopy.copy($sh3_wb1) # copy source sheet to destination workbook

    $sh4_wb1 = $wb1.sheets.item(4) # fourth sheet in destination workbook 
    $sheetToCopy = $wb2.sheets.item('MCH') # source sheet to copy 
    $sheetToCopy.copy($sh4_wb1) # copy source sheet to destination workbook

    $wb2.close($false) # close source workbook w/o saving 
    ###########################################################################
                    ####Copy MSP04 CRA, HOSP, FMP, MCH####
    $sh5_wb1 = $wb1.sheets.item(5) # fifth sheet in destination workbook 
    $sheetToCopy = $wb3.sheets.item('CRA') # source sheet to copy
    $sheetToCopy.copy($sh5_wb1) # copy source sheet to destination workbook

    $sh6_wb1 = $wb1.sheets.item(6) # sixth sheet in destination workbook
    $sheetToCopy = $wb3.sheets.item('HOSP') # source sheet to copy
    $sheetToCopy.copy($sh6_wb1) # copy source sheet to destination workbook 

    $sh7_wb1 = $wb1.sheets.item(7) # seventh sheet in destination workbook 
    $sheetToCopy = $wb3.sheets.item('FMP') # source sheet to copy 
    $sheetToCopy.copy($sh7_wb1) # copy source sheet to destination workbook

    $sh8_wb1 = $wb1.sheets.item(8) # eighth sheet in destination workbook 
    $sheetToCopy = $wb3.sheets.item('MCH') # source sheet to copy 
    $sheetToCopy.copy($sh8_wb1) # copy source sheet to destination workbook

    $wb3.close($false) # close source workbook w/o saving 
    ###########################################################################
                     ####Copy MSP05 CRA, HOSP, FMP, MCH####
    $sh9_wb1 = $wb1.sheets.item(9) # nineth sheet in destination workbook 
    $sheetToCopy = $wb4.sheets.item('CRA') # source sheet to copy
    $sheetToCopy.copy($sh9_wb1) # copy source sheet to destination workbook

    $sh10_wb1 = $wb1.sheets.item(10) # tenth sheet in destination workbook
    $sheetToCopy = $wb4.sheets.item('HOSP') # source sheet to copy
    $sheetToCopy.copy($sh10_wb1) # copy source sheet to destination workbook

    $sh11_wb1 = $wb1.sheets.item(11) # eleventh sheet in destination workbook 
    $sheetToCopy = $wb4.sheets.item('FMP') # source sheet to copy 
    $sheetToCopy.copy($sh11_wb1) # copy source sheet to destination workbook

    $sh12_wb1 = $wb1.sheets.item(12) # twelveth sheet in destination workbook 
    $sheetToCopy = $wb4.sheets.item('MCH') # source sheet to copy 
    $sheetToCopy.copy($s12_wb1) # copy source sheet to destination workbook

    $wb4.close($false) # close source workbook w/o saving 
    ###########################################################################
                    ####Copy MSP06 CRA, HOSP, FMP, MCH####
    $sh13_wb1 = $wb1.sheets.item(13) # thirteenth sheet in destination workbook 
    $sheetToCopy = $wb5.sheets.item('CRA') # source sheet to copy
    $sheetToCopy.copy($sh13_wb1) # copy source sheet to destination workbook

    $sh14_wb1 = $wb1.sheets.item(14) # fourteenth sheet in destination workbook
    $sheetToCopy = $wb5.sheets.item('HOSP') # source sheet to copy
    $sheetToCopy.copy($sh14_wb1) # copy source sheet to destination workbook

    $sh15_wb1 = $wb1.sheets.item(15) # fifteenth sheet in destination workbook 
    $sheetToCopy = $wb5.sheets.item('FMP') # source sheet to copy 
    $sheetToCopy.copy($sh15_wb1) # copy source sheet to destination workbook

    $sh16_wb1 = $wb1.sheets.item(16) # sixteenth sheet in destination workbook 
    $sheetToCopy = $wb5.sheets.item('MCH') # source sheet to copy 
    $sheetToCopy.copy($sh16_wb1) # copy source sheet to destination workbook

    $wb5.close($false) # close source workbook w/o saving
    ###########################################################################

    $sh1_wb1.Name ="MSP03-CRA"
    $sh2_wb1.Name ="MSP03-HOSP"
    $sh3_wb1.Name ="MSP03-FMP"
    $sh4_wb1.Name ="MSP03-MCH"
    $sh5_wb1.Name ="MSP04-CRA"
    $sh6_wb1.Name ="MSP04-HOSP"
    $sh7_wb1.Name ="MSP04-FMP"
    $sh8_wb1.Name ="MSP04-MCH"
    $sh9_wb1.Name ="MSP05-CRA"
    $sh10_wb1.Name ="MSP05-HOSP"
    $sh11_wb1.Name ="MSP05-FMP"
    $sh12_wb1.Name ="MSP05-MCH"
    $sh13_wb1.Name ="MSP06-CRA"
    $sh14_wb1.Name ="MSP06-HOSP"
    $sh15_wb1.Name ="MSP06-FMP"
    $sh16_wb1.Name ="MSP06-MCH"
    ###########################################################################
    $wb1.close($true) # close and save destination workbook 
    $xl.quit() 
    Thursday, December 28, 2017 8:08 PM

Answers

  • This is all you need to do.

    $files = 'Test.xls','Test.xls' ,'Test.xls','Test.xls','Test.xls' 
    
    $xl = new-object -ComObject excel.application
    $xl.displayAlerts = $false 
    $target = $xl.workbooks.open('c:\test\target.xlsx')
    
    foreach($file in $files){
        $source = $xl.workbooks.open($file1, $null, $true)
        $source.sheets |
            ForEach-Object{
                # build fname from array or from file name.
                $_.Name = $fname + '-' + $_.Name
                $_.copy($target.sheets[$target.sheets.Count]) # this always adds each sheet at the end no matter how man y sheets there are.
            }
        $source.Close()
    }
    $target.close($true) # close and save destination workbook 
    $xl.quit() 


    \_(ツ)_/

    Thursday, December 28, 2017 8:34 PM

All replies

  • Make the workbook visible.  Step through the code in the debugger.  I think you will quickly see what is happening.

    I recommend just looping through the sheets in each book and copying them.  They will copy in order to the target.  Rename them as you copy and always check for errors.

    Only open one source at a time and close it before opening the next book. 

    All of this will create very compact and simple to understand code.


    \_(ツ)_/

    Thursday, December 28, 2017 8:17 PM
  • I should also note that the worksheet should be renamed before copying and it will  be copied to the position after the specified target sheet.

    \_(ツ)_/

    Thursday, December 28, 2017 8:26 PM
  • This is all you need to do.

    $files = 'Test.xls','Test.xls' ,'Test.xls','Test.xls','Test.xls' 
    
    $xl = new-object -ComObject excel.application
    $xl.displayAlerts = $false 
    $target = $xl.workbooks.open('c:\test\target.xlsx')
    
    foreach($file in $files){
        $source = $xl.workbooks.open($file1, $null, $true)
        $source.sheets |
            ForEach-Object{
                # build fname from array or from file name.
                $_.Name = $fname + '-' + $_.Name
                $_.copy($target.sheets[$target.sheets.Count]) # this always adds each sheet at the end no matter how man y sheets there are.
            }
        $source.Close()
    }
    $target.close($true) # close and save destination workbook 
    $xl.quit() 


    \_(ツ)_/

    Thursday, December 28, 2017 8:34 PM
  • This is very helpful.  I am now able to get a number of worksheets copied, but it copies all of them.  How can I get specific ones copied, but still use the function created.

    $files='Test.xls','Test.xls','Test.xls','Test.xls'

    $fname='MSP03','MSP04','MSP05','MSP06' #What I want to add to front of copied sheets

    $wksht = 'CRA','MCH','HOSP','FMP'##Sheets I want to copy from source workbook

    $xl = new-object -ComObject excel.application
    $xl.displayAlerts = $false 
    $target = $xl.workbooks.open('\\wwt-fp01.wwtps.com\users$\asalda9\Desktop\Excel_PS_Testing\Dest_Wkbk.xlsx')

    foreach($file in $files){
        $source = $xl.workbooks.open($file, $null, $true)
        $source.sheets|
            ForEach-Object{
                $_.Name = $fname + '-' + $_.Name
                $_.copy($target.sheets[$target.sheets.Count]) # this always adds each sheet at the end no matter how man y sheets there are.
            }
        $source.Close()
    }
    $target.close($true) # close and save destination workbook 
    $xl.quit() 

    Friday, December 29, 2017 6:27 PM
  • Use an array of names:

    $sheetnames = 'CRA','HOSP','FMP','MCH'

    $source.sheets | Where{ $_.Name -in $sheetnames } |


    \_(ツ)_/

    Friday, December 29, 2017 6:42 PM
  • Thank you for all your help.  I was able to get this to work off your support.  I will now work on getting this to show up in a form to have a user select different files each time and then have the sheets show up to select which ones would get put together.  If you have any advice please let me know.  I was looking into possibly using hash tables which would be new for me. Which I could give you more votes!

    foreach($file in $files){

        $source = $xl.workbooks.open($file, $null, $true)
        $source.sheets|Where {$_.Name -in $sheetnames}|
            ForEach-Object{
                $_.Name = ((Split-Path $file -leaf).split(" "))[0] + '-' + $_.Name
                $_.copy($target.sheets[$target.sheets.Count]) # this always adds each sheet at the end no matter how man y sheets there are.
            }
        $source.Close()
    }
    $target.close($true) # close and save destination workbook 
    $xl.quit() 


    • Edited by Fyrehawk24 Friday, December 29, 2017 8:19 PM
    Friday, December 29, 2017 8:19 PM
  • Hash tables are good for name=value" pairs.  All names must be unique in a hash.

    In a form just use the controls as the source.  a CheckedListBox comes to mind.  Beyond that I cannot guess at your design.Here isa  good source for info on forms design.

    https://info.sapien.com/index.php/guis/gui-design-best-practice/user-interface-design-for-administrators

    Search for other blogs and posts on forms designs.  DO NOT use WPF if you are not an experienced programmer.


    \_(ツ)_/

    Friday, December 29, 2017 8:46 PM