Saving Excel Package

[efigueredo]

I just updated to the latest version of ImportExcel
My script was previously able to save successfully and as of the new update, saving fails.
The lack of detail makes it very hard to troubleshoot what the cause might be.

====
Exception calling "Save" with "0" argument(s): "Error saving file

ImportExcel\7.8.10\Public\Close-ExcelPackage.ps1:29 char:20

•         else { $ExcelPackage.Save() }
  

•                ~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : InvalidOperationException

[dfinke]

no idea. if you roll back to the other version, does it work?

[efigueredo]

I have to keep digging. The issue might be related to formula I am using in excel columns, but I have not narrowed down the issue yet. I do not think it is the number of columns which is around 56. I have simpler tables with more columns. I wish the error messages were more detailed, it makes troubleshooting very difficult. Sent from my Verizon, Samsung Galaxy smartphone

…

-------- Original message -------- From: Doug Finke ***@***.***> Date: 10/23/24 4:38 PM (GMT-05:00) To: dfinke/ImportExcel ***@***.***> Cc: Eduardo Luis Figueredo ***@***.***>, Author ***@***.***> Subject: Re: [dfinke/ImportExcel] Saving Excel Package (Issue #1649) no idea. if you roll back to the other version, does it work? — Reply to this email directly, view it on GitHub<#1649 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACW4WWGGRCNFSQOGQJPDINTZ5ACLRAVCNFSM6AAAAABQO6GX22VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMZTGM4TOOJYHE>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[efigueredo]

That said.....I love ImportExcel!!! Sent from my Verizon, Samsung Galaxy smartphone

…

-------- Original message -------- From: Doug Finke ***@***.***> Date: 10/23/24 4:38 PM (GMT-05:00) To: dfinke/ImportExcel ***@***.***> Cc: Eduardo Luis Figueredo ***@***.***>, Author ***@***.***> Subject: Re: [dfinke/ImportExcel] Saving Excel Package (Issue #1649) no idea. if you roll back to the other version, does it work? — Reply to this email directly, view it on GitHub<#1649 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACW4WWGGRCNFSQOGQJPDINTZ5ACLRAVCNFSM6AAAAABQO6GX22VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMZTGM4TOOJYHE>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[dfinke]

if possible, send me the xlsx. may be possible to import that error message,

[efigueredo]

Thanks for the offer. But it chokes before it can create the xlsx. So I have to back track items in the codes until it works again to narrow down the issue. Sent from my Verizon, Samsung Galaxy smartphone

…

-------- Original message -------- From: Doug Finke ***@***.***> Date: 10/23/24 6:44 PM (GMT-05:00) To: dfinke/ImportExcel ***@***.***> Cc: Eduardo Luis Figueredo ***@***.***>, Author ***@***.***> Subject: Re: [dfinke/ImportExcel] Saving Excel Package (Issue #1649) if possible, send me the xlsx. may be possible to import that error message, — Reply to this email directly, view it on GitHub<#1649 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACW4WWAADA55FKRWQG5OEDDZ5ARDFAVCNFSM6AAAAABQO6GX22VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMZTGY3DANZZGQ>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[efigueredo]

Doug, Following up on the Excel file error. I have a script that creates an Excel file with multiple worksheets. Each worksheet has exactly one Excel table on it. Most columns have values. Some columns have formula. The problem is that if I format the formula incorrectly, it can fail to create the excel file and the error message generated does not give me a clue where the error is. Ideally the error message would at least tell me which worksheet might be the culprit. In a perfect world it would tell me which column or formula is the issue. Do you have any way to provide more explicit error information? Thank you, Eduardo Figueredo From: Doug Finke ***@***.***> Sent: Wednesday, October 23, 2024 6:44 PM To: dfinke/ImportExcel ***@***.***> Cc: Eduardo Luis Figueredo ***@***.***>; Author ***@***.***> Subject: Re: [dfinke/ImportExcel] Saving Excel Package (Issue #1649) if possible, send me the xlsx. may be possible to import that error message, — Reply to this email directly, view it on GitHub<#1649 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACW4WWAADA55FKRWQG5OEDDZ5ARDFAVCNFSM6AAAAABQO6GX22VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMZTGY3DANZZGQ>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>>

[dfinke]

can you create an xlsx with 2 sheets, one that is good an the other with the bad formula?

[efigueredo]

Doug, I have another question….I have made good progress. I am trying to put an Excel formula into a cell. For example: [OfficeOpenXml.ExcelRange] $xlValueCell = $ws.Cells["$($v)$($r+2)"] $xlValueCell.Formula = “=COUNTA(tblClusters[Cluster])” At the time of adding this formula to the cell, the referenced Excel table already exists along with the column name. However, when, the file is exported, and opened in Excel what I see in the formula for that cell is: “=COUNTA(#REF!)” If I then manually create the formula in Excel, it works without any problem. Do you know if OpenOffice supports using named table ranges? Thank you, Eduardo Figueredo From: Doug Finke ***@***.***> Sent: Tuesday, November 5, 2024 9:25 AM To: dfinke/ImportExcel ***@***.***> Cc: Eduardo Luis Figueredo ***@***.***>; Author ***@***.***> Subject: Re: [dfinke/ImportExcel] Saving Excel Package (Issue #1649) can you create an xlsx with 2 sheets, one that is good an the other with the bad formula? — Reply to this email directly, view it on GitHub<#1649 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACW4WWF5KJLMEPGWZQYVIR3Z7DINRAVCNFSM6AAAAABQO6GX22VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJXGMYTOMBUGA>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[dfinke]

Thanks for the question @efigueredo.

Is that a pre-existing workbook?

On the Export-Excel there is a switch AutoNameRange that creates names.

I believe I do it at the workbook level.

The $xlValueCell.Formula = “=COUNTA(tblClusters[Cluster])” - iirc there may be addtional syntax to make that work, check the Examples folder in the repo.