Saving Excel Package
efigueredo opened this issue · 10 comments
efigueredo commented
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 commented
no idea. if you roll back to the other version, does it work?
efigueredo commented
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 commented
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 commented
if possible, send me the xlsx. may be possible to import that error message,
efigueredo commented
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: ***@***.***>
YeharaDananjaya commented
maybe this solution will solve your problem click
efigueredo commented
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 commented
can you create an xlsx with 2 sheets, one that is good an the other with the bad formula?
efigueredo commented
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 commented
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.