Copy 2 Excel Worksheets at the same time to a new workbook

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Matt Bloomfield Matt Bloomfield 2 weeks, 2 days ago.

  • Author
    Posts
  • #74561
    Profile photo of Steven Rivera
    Steven Rivera
    Participant

    To explain, I currently have like 90 different files that I need to, after processing, separate the first two tabs of the file and copy paste to a new workbook and save that workbook, with the name of the person who the report will go to (name is in each of the files) and email them out.

    I've been able to figure and or find the answer for everything except for copying two worksheets at the same time. This is crucial as the first worksheet is dependent on the second worksheet.

    Can anyone help with this? Below is my code for the copy sheets to new workbook section (I know it's not pretty, am a noob)

    $Files = GCI '\\Networkfolder\Dashboards' | ?{$_.Extension -Match "xlsx?"} | select -ExpandProperty FullName
    
    #Launch Excel, and make it do as its told (supress confirmations)
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $True
    $Excel.DisplayAlerts = $False
    
    #Loop through files and perform tasks
    ForEach($File in $Files[0..4]){
    $Source = $Excel.Workbooks.Open($File,$true,$true) #Open's source
    $NewWorkBook = $Excel.Workbooks.Add() # open target (New Workbook)
    
    $sh1_wb1 = $NewWorkBook.sheets.item(1) # first sheet in destination workbook
    
    $sheetToCopy2 = $Source.sheets.item(2) # source sheet to copy
    $sheetToCopy = $Source.sheets.item(1) # source sheet to copy
    $SheetsToCopy = ($sheetToCopy2, $sheetToCopy)
                   
    $SheetsToCopy.copy($sh1_wb1) # copy source sheet to destination workbook
    
    $ws1 = $NewWorkBook.worksheets | where {$_.name -eq "Charts Data Tab"} #Selects Second Sheet
    $ws2 = $NewWorkBook.worksheets | where {$_.name -eq "Dashboard"} #Selects First Sheet
    $ws3 = $NewWorkBook.worksheets | where {$_.name -eq "Sheet1"} #Selects Sheet1
    
        $range = $ws1.Rows.Item("1:5000")
        [void]$range.select()
        $range.Copy();
        $range.PasteSpecial(-4163)
    
    $range1 = $ws1.range("p2" )
    $range1.entireColumn.Autofit() 
    $range2 = $ws1.range("w2" )
    $range2.entireColumn.Autofit() 
    $range3 = $ws1.range("Ah2" )
    $range3.entireColumn.Autofit() 
    $range4 = $ws1.range("Ap2" )
    $range4.entireColumn.Autofit() 
    $range5 = $ws1.range("bf2" )
    $range5.entireColumn.Autofit() 
    $range6 = $ws1.range("bl2:bo2" )
    $range6.entireColumn.Autofit() 
    $range7 = $ws1.range("br2:bu2" )
    $range7.entireColumn.Autofit() 
    $range8 = $ws1.range("bx2:ca2" )
    $range8.entireColumn.Autofit() 
    
    $Name = $ws1.Cells.Item.Invoke(2,4).Value2  #Registers contents of D2 of Sheet2
    
    $FName = "\\networkfolder\Testing\$Name.xlsx"
    
    $range9 = $ws2.range("u2" ).select()
    $range9.entireColumn.Autofit() 
    
    $ws3.Delete()
    
    $NewWorkBook.SaveAs($FName)
    $Source.close($false) # close source workbook w/o saving
    Start-Sleep 50000
    }
    $xl.quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
    
  • #74704
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Ugh. Excel Automation, the decade-old, deprecated code that refuses to die. Almost makes you long for Access.

    From what I can tell, there's no way to copy two worksheets at once using this sucker. That said, it shouldn't be a problem to copy one and then the other. The references may fail until both are copied, but they should "heal" afterwards. The formulas don't "die" just because they can't find their references; they'll just stop working until the references resolve again.

    • #74711
      Profile photo of Steven Rivera
      Steven Rivera
      Participant

      The code I placed currently does copy the sheets one at a time. The issue is that the charts are looking at the "source" file. When I distribute to the audience and they double click on a chart to see the details, it will look for that source file instead of looking at the 2nd tab of that same workbook...

      As an alternative (just thought of this now, while typing), is there a way to do a "Replace All"? I think if I can remove the address from the formula, I can force it to look at the second tab.

  • #74721
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    Have a look at the Import-Excel module by Doug Finke: https://github.com/dfinke/ImportExcel

    I'm not too sure what the rest of your code is doing but to copy two sheets from one workbook to another is as simple as:

    Import-Excel E:\testBook1.xlsx -WorkSheetname Sheet1 | Export-Excel E:\testBook2.xlsx -WorkSheetname Sheet1
    Import-Excel E:\testBook1.xlsx -WorkSheetname Sheet2 | Export-Excel E:\testBook2.xlsx -WorkSheetname Sheet2

You must be logged in to reply to this topic.