Free Support Forum - aspose.com

UDF problem

Aspose.Cells Product Family
#1

I have a template file with a user defined function. I'm using Aspose to generate a multi-page workbook, by copying the sheets in the template file. I've written a custom function that handles the calculation for the UDF within Aspose. The problem is that, when the copied sheets are opened in Excel the cells referencing the UDF show a #NAME error. The UDF is there, and in fact if I go to any of the cells referencing the UDF and simply delete and add the last character in the line, the UDF is recognized and calculated.

Note that if I leave the template sheets in the workbook, they open and correctly display the calculated value. Only the copied sheets display the #NAME error.

Rick

#2

Hi Rick,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please share your template file and sample code with us to reproduce the issue. We will check it and get back to you soon.

Thank You & Best Regards,

#3

The attached file includes a program to show the issue, an Excel file used as a template and an Excel file with the error.

The program simply opens the template, copies the sheet, and saves a new Excel file. If you open the output file and scroll down to row 122, you can see that on the original sheet, all of the cells referencing the UDF display correctly. In the sheet added by Aspose, all of those same cells show a #NAME error. If you simply delete and retype a character within one of the cells, it fixes the problem in that cell. I'd added code in the Excel file Auto_Open macro as a workaround (the code is still there in Module1 but commented out), which simply copied each of the cells and pasted them back, but this causes other problems when I protect the sheets.

Note that, within Aspose, the formula does correctly calculate using a custom function, on both the original sheet and on the copied sheet.

Rick

#4

Hi Rick,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for sharing the sample application.

We have found your mentioned issue after an initial test. We will look into it and get back to you soon. Your issue has been registered in our internal issue tracking system with issue id: CELLSNET-15519.

Thank You & Best Regards,

#5

Hi Rick,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please set the workbook.Settings. ReCalcOnOpen property as false before saving the file, otherwise MS Excel will calculate the formula again when loading the file.

<?xml:namespace prefix = u1 />

Please check the following code:

Workbook workbook = new Workbook();

workbook.Open(@"F:\FileTemp\AsposeShaleMonthly.xls");

workbook.Worksheets.AddCopy("Sheet1");

workbook.Settings.ReCalcOnOpen = false;

workbook.Save(@"F:\FileTemp\dest.xls");

And we will look into the issue that why MS Excel does not re-calculate the formulas when loading the template file.

Thank You & Best Regards,

#6

I tried turning off RecalcOnOpen, but there are still problems. For example, I changed the code in the example I sent you to:

Dim strFN As String = strMyDoc & "\AsposeCopyError.xls"
wbTemplate.Settings.ReCalcOnOpen = False
wbTemplate.Save(strFN, FileFormatType.Excel97To2003)

(I added the middle line in between the 2 existing lines in the file I sent).

While the file does appear to be ok, if you go to the page added (Sheet2), click on the Data Load Sheet button, then click the Load LCI button, you'll get type mismatch error, and if you then go look at the rows with that formula the #NAME error will be in each of those cells. If you do the same steps on Sheet1 (the original sheet) it will work. The buttons both go to the same vba routine, which simply does a copy of the formula row, then a paste special to the row below it. Again, if I simply delete the trailing parenthesis and type it back in, the formula will calculate correctly.

Rick

#7

Hi Rick,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for the feedback.

We have found your mentioned issue. We will look into it and get back to you soon.

Thank You & Best Regards,

#8

Hi,

Please try the attached version, we have resolved the issue.

Thank you.

#9

The same problem still exists. Make sure you have commented out the Auto_Run macro in the workbook as I have a workaround in there to 'fix' the problem at startup. If that macro runs it hides the problem, but if that macro is commented I still get #NAME errors.

Rick

#10

Hi,

We will look into it and get back to you soon.

Thank you.

#11

I apologize, it is working correctly. Even though I added the new dll, and it displayed the correct version, it was somehow still using a version several revisions back. I deleted the reference and added it back in and it now seems to be ok. Again, sorry.

Rick

#12

Hi,

OK, thanks for the confirmation that your issue is actually resolved. It will surely save our time to further analyze/look into your issue because it works on our end.

Thanks again.