Adding a worksheet to existing file

[MarianiVentura]

The code below shows how I created two separate Excel files with sheets and pivot tables, $excel and $newexcel .
I now want to combine them into a single Excel file.

What is the correct syntax to add my $newexcel, INBDate sheet to the existing $excel file?

Thanks for an awesome tool!

$pt = [ordered]@{}
 
#take the csv file and make an Excel file


$pt.INBounds = @{ SourceWorkSheet = 'Items';
    PivotRows                     = 'INBNo', 'Vendor'
    PivotColumns                  = 'Location'
    PivotData                     = @{'BOFilledValue' = 'sum' }
    PivotFilter                   = 'Days'
}
$pt.Vendor = @{ SourceWorkSheet = 'Items'
    PivotRows                   = 'Vendor'
    PivotData                   = @{'BOFilledValue' = 'sum'
        'ItemsNoBackorder'        = 'sum'
    }
                       
}

$excel = Import-Csv $output_file -Delimiter ^ | Export-Excel $excel_file -Title $title -WorksheetName 'Items' -AutoSize -AutoNameRange -TableName Items -FreezePane 3 -Passthru `
    -PivotTableDefinition $pt 
           

$Frow = $rowNum + 1

$sheet = $excel.Workbook.Worksheets["Items"]
$sheet.Column(1) | Set-ExcelRange -NFormat "#####.00" #ItemNo
$sheet.Column(8) | Set-ExcelRange -NFormat "$#,###." -Hidden #Avg Rate
$sheet.Column(9) | Set-ExcelRange -NFormat "$#,###." -Hidden #Base Price
$sheet.Column(13) | Set-ExcelRange -NFormat "$###,###,###."
$sheet.Column(14) | Set-ExcelRange -NFormat "$###,###,###."
$sheet.Column(15) | Set-ExcelRange -NFormat "$#,###."
$sheet.Column(16) | Set-ExcelRange -NFormat "$#,###."
$sheet.Column(17) | Set-ExcelRange -NFormat "$#,###."
$sheet.Column(20) | Set-ExcelRange -NFormat "#,###"


Set-ExcelRange -Range $sheet.Cells["M1"]  -Formula "=SUBTOTAL(9,M3:M$Frow)" -NumberFormat "$###,###,###." -Bold
Set-ExcelRange -Range $sheet.Cells["N1"]  -Formula "=SUBTOTAL(9,N3:N$Frow)" -NumberFormat "$###,###,###." -Bold
Set-ExcelRange -Range $sheet.Cells["O1"]  -Formula "=SUBTOTAL(9,O3:O$Frow)" -BackgroundColor yellow -NumberFormat "$###,###,###." -Bold
Set-ExcelRange -Range $sheet.Cells["P1"]  -Formula "=SUBTOTAL(9,P3:P$Frow)" -NumberFormat "$###,###,###." -Bold
Set-ExcelRange -Range $sheet.Cells["Q1"]  -Formula "=SUBTOTAL(9,Q3:Q$Frow)" -NumberFormat "$###,###,###." -Bold

Close-ExcelPackage -ExcelPackage $excel

$newexcel = Import-Csv $output_inb_file -Delimiter ^ | Export-Excel $excel_inbdate_file -Title "Inbounds By Date" -WorksheetName 'INBDate' -AutoSize -AutoNameRange -TableName "INBDates" -FreezePane 3 -Passthru
$sheet = $newexcel.Workbook.Worksheets["INBDate"]
$sheet.Column(4) | Set-ExcelRange -NFormat "$###,###,###."
Set-ExcelRange -Range $sheet.Cells["D1"]  -Formula "=SUBTOTAL(9,D3:D500)" -NumberFormat "$###,###,###." -Bold

Close-ExcelPackage -ExcelPackage $newexcel

[cristtos]

I believe that if the Excel file $FinalFilePath exists and the worksheet in -WorksheetName does not exist - it will be just added in the existing file.
In my case I prepare a $DataTable extract from SQL and copy an existing "template" file into the working directory as $FinalFilePath
Then I run:
$DataTable | Export-Excel $FinalFilePath -WorksheetName "NewSheet"
This adds the new NewSheet and populates it.
Once this is done I use

$workbook = Open-ExcelPackage -Path $FinalFilePath
$CurrentPage = $workbook.Workbook.Worksheets["NewSheet"]

to manipulate elements of the NewSheet (format, values, formulae):

$range = $CurrentPage.Cells["F1"]
$range.Value = ("Configuration as of Today " + (Get-Date).ToString("yyyyMMdd")) 

When finished:

$workbook.Save()
Close-ExcelPackage $workbook -Calculate
$workbook.Dispose()

Let me know if this works for you.

[MarianiVentura]

@cristtos - thank you for taking the time to reply, it is appreciated. I used your notes to fix my issue. I was getting errors about the Pivot Tables when I opened the Excel file. I simply swapped and created the workbook with the new sheet first and then added the other sheets and pivot tables from my original workbook and it worked.

[dfinke]

Eyeballing that, it looks good. Would need data and a short script that repros what you are looking for in the end result.

[MarianiVentura]

Thank you always Mr. Finke for such a cool module!