Exception calling "Save" with "0" argument(s)

[jtst1]

So I have this powershell script that I use to manage my budget spread sheet and I've recently started running into the below error. The code below produces the same error hence why I put it instead of my budget code. It's weird if I make a new xlsx file, copy the data from my test.xlsx, then use the same code below but point to the new file it works....but just once. If I run it again it produces the error.

Any assistance is greatly appreciated.

Also this is my first Issue post on github, so be gentle.

$path = "Z:\test.xlsx"
$excel = Open-ExcelPackage -Path $path
$excel.Sheet1.Cells['C20'].Value = "test"
Close-ExcelPackage $excel

Close-ExcelPackage $excel
Exception calling "Save" with "0" argument(s): "Error saving file Z:\test.xlsx"
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.6\Public\Close-ExcelPackage.ps1:30 char:13
+             $ExcelPackage.Save()
+             ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

[dfinke]

I would need to see the xlsx in order to debug the issue.

Can you repro the problem on a sanitized xlsx and post it?

[TWalijew]

I get this err msg when I try to save when the underlying XLSX still is open (and therefore locked).

[dfinke]

If you have thexlsx open in Excel, that will fail. Excel has it locked and the Close-ExcelPackage needs to save the actual file.

[ghost]

see edit2 below
@dfinke , Doug - how do we troubleshoot errors that prevent save (or $excelPackage.SaveAs($newFilePath)?

I have an xlsx where one of the worksheets is preventing the powershell module to save.

There must be an unsupported element in this specific sheet since I can remove the "problem" sheet in the Excel application and then the module is able to handle the xlsx and save without an issue.

The "problem" sheet does not have any macro or obvious complexity that the module would be unable to handle.

edit: to be clear, my code does not interact with the problem sheet. I have a feeling that any modification to the xlsx requires the entire package to be rebuilt/packaged together and breaks due to a specific element on one of the (problem) sheets.

edit2: i found the issue was caused by a table in the "problem" sheet.
on the "Design" tab of Excel, "Convert to Range". now , ImportExcel is able to handle the xlsx, love GOD.
i did not design the excel file im working with but everything seems normal after convert to range.
<3

thank you for your time.

[dfinke]

Did that resolve your issue. Debugging this sorts of things is just how you did it.

[ghost]

Did that resolve your issue. Debugging this sorts of things is just how you did it.

yes, "Convert to Range" resolved my issue - but i'm not the original person to open this issue. i happened to have the same error, "Exception calling "Save" with "0" argument(s)" with "InvalidOperationException" - but OP could have an unrelated issue preventing his save.

if i could give feedback:

1. amazing module, great work. i respect all your work and response to my message. thank you.
2. if "-verbose" is not an available parameter for Close-ExcelPackage , i think it would be helpful to include a list of "known" issues/unsupported xlsx elements in a FAQ page. Maybe my table problem was common knowledge (or unique to my situation), not sure.

thanks again.

[dfinke]

Thanks @jhnkane good poing about an FAQ.

If there was a -Verbose switch on Close-ExcelPackage. What details would you like to see?

[benjaminselby]

I also have this issue, but it seems to possibly be connected to cell validation(s). Strangely, it seems to shift around. When I remove cell validation from a particular problem cell, it might fix the issue sometimes, but at other times it doesn't.

I'll try to keep digging and will see if I can get any further info. Sorry that this is not more specific.

[dfinke]

hmm, a shape shifting bug 😉