Can't save excel file after delete of Worksheet

[RaZz85]

Hi,

I'm running into issues trying to save an excelfile after editing it with PSExcel. I'm trying to get rid of the excess worksheet PSExcel generates when creating a graph, since I'll be using the same dataset to generate a couple of different graphs, and want to avoid having 5 identical worksheets in there.

The following works perfectly:

$Xl = New-Excel -Path C:\temp\report.xlsx 
$WorkBook = $Xl | get-workbook
$WorkSheet = $Workbook | Get-Worksheet -Name "PivotTable1"
$WorkSheet.Name = "DB In Use"
$Xl | save-Excel -close

My pivottable worksheet is renamed to the name I want it to have. But when I do the following, I get an error on saving the excel file:

$Xl = New-Excel -Path C:\temp\report.xlsx 
$WorkBook = $Xl | get-workbook
$WorkSheet = $Workbook | Get-Worksheet -Name "Data1"
$WorkBook.Worksheets.Delete($WorkSheet)
$Xl | save-Excel -close

The script fails on the last line: `Save-Excel : Error saving file. Exception calling "Save" with "0" argument(s): "Error saving file C:\temp\demo.xlsx" At R:_Scriptrepository\Under_Construction\Tondelje\ExchangeEnvironmentReport.ps1:158 char:17

• $Xl | save-Excel <<<< -close
  • CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
  • FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Save-Excel`

The issue also occurs when using $Workbook.Worksheets.Delete("Data1").

At first I thought this might've been an issue with EPPlus so I've updated to the last version but to no avail.

Thank you for your help!

[willemdh]

I'm having a similar problem.

$Outputdate = (Get-Date -Format 'yyyyMMdd.HHmmss')
$InvStruct.InvData | Export-XLSX -Path "$($InvSTruct.outputfolder)\Nagios_Inventory_$outputdate.xlsx" -Table -Autofit -Force
$Excel = New-Excel -Path "$($InvSTruct.outputfolder)\DAF_Nagios_Inventory_$Outputdate.xlsx"
$Excel | Get-WorkSheet | Format-Cell -Header -Bold $True -Size 12
$Excel | Get-WorkSheet | Format-Cell -StartColumn 1 -EndColumn 20 -Autofit -AutofitMinWidth 3 -AutofitMaxWidth 60
$Worksheet = $Excel | Get-Worksheet
$WorkSheet | Set-FreezePane -Row 2
$Excel | Save-Excel -Close

Save-Excel : Error saving file. Exception calling "Save" with "0" argument(s): "Error saving file

[RamblingCookieMonster]

Yeah, sorry about that, s/18/48, sorry!

[willemdh]

@RamblingCookieMonster Np at all, thanks for the fix. I'll try it out asap.

[RamblingCookieMonster]

Oh! To clarify, the commit was for #48, not this one!

That is strange that it doesn't like the delete method - will have to check that out!

[willemdh]

@RamblingCookieMonster I did test the new release anyway and it indeed does not fix this issue. Stil getting:

Save-Excel : Error saving file. Exception calling "Save" with "0" argument(s): "Error saving file ...

When using $Excel | Save-Excel -Close

[kaushiksanthosh]

@RamblingCookieMonster I am facing the same issue. 1st I got it for Format-Cell -Autofit and now am getting it for Close-Excel -Save. Thing to notice here is, my script runs on a loop and it fails intermittently.

Any help on this please?

[jaydeepch15]

@RamblingCookieMonster Thanks for the code, However -Save issue is still persists.

$Excel | Close-Excel -Save OR $Excel | Save-Excel -Close

Both giving same error

`Save-Excel : Error saving file. Exception calling "Save" with "0" argument(s): "Error saving file     C:\Scripts\PNE.xlsx"
At line:1 char:10
+ $Excel | Save-Excel -Close
+          ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Save-Excel

`