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

PowerShell Help
1

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)
2

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.

3

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.

4

Have a look at the Import-Excel module by Doug Finke: GitHub - dfinke/ImportExcel: PowerShell module to import/export Excel spreadsheets, without Excel

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