Copy-ExcelWorkSheet destination table as values

[jinjuujii]

My source table has formulas in the cells and with the destination table, the values won't carry and result in REF.

I looked at the help command and I don't think there's a paste value parameter.

My current solution right now is seeing if I can just looping through my original sheets and do a copy/paste as value and then run my Copy-ExcelWorksheet.

Does anyone have another possible solution for me?

Edit 1:
I use Import-Excel to store the sheet and then pipe it to Export-Excel. This retains all values, however, we lose the original format of the sheet.

[HLaksmana]

Open-ExcelPackage with the original file, save it as a copy, and use export-excel on the copy to put in the values.

[jinjuujii]

Do you mean open the ExcelPackage and then do a WB.SaveAs('.\newname.xlsx')? Doing so just copy the original, did not replace the formulas for me.

[HLaksmana]

Include steps 2 and 3 as well.

$newWB = Open-ExcelPackage 'oldwb.xlsx'
Import-Excel -ExcelPackage $newWB | Export-Excel -ExcelPackage $newWB -PassThru | Close-ExcelPackage -SaveAs 'newwb.xlsx'