Formula to external workbook

Aspose.Cells Product Family
1

Hi,

I'm working on an application that creates Excel-files. Most of the time several workbooks are created. Those workbooks are linked to eachother using formula's in cells pointing to specific named ranges in the linked workbook (eg. <='c:\wbook1.xls'!SomeNamedRange>).

During implementation of the Aspose.Cells component, the linking failed. After some more investigation it appears Aspose is adding some extra single-quotes to the formula. So the formula above will result in <=''C:\WBOOK1.XLS'!SOMENAMEDRANGE'> (behind the equal-sign are 2 single-quotes). This is not a correct formula in Excel so the output in this cell will always be "#NAME?".

After some searching on this site I found an article which shows how to add external formula's. I tried this example and it worked. The only difference with the example is the formula points to a specific sheet <='c:\wbook1.xls'Source!A1>. So those formula's do work, but a formula without a sheet reference won't work. All my named ranges are global so I am reluctant to use a sheet reference.

Could you look into this?

Attached is a sample project showing the problem.

Thanks in advance,

Timo

2

Hi Timo,

Thanks for providing us the sample project.

We found the issue regarding setting external referenced formula that contains named range(s). Obviously, it works fine when setting external referenced formula that simply contains worksheet cell(s).

We will look into the issue to figure it out soon.


Thank you.

3

Hi Amjad,

Any news on this issue? We can't make much progress without this functionality and I can't think of a work-around this time...

Thanks,

Timo

4

Hi Timo,

We will update you soon regarding your issue.

Thanks for being patient!

5

Hi,

We are working on external referenced formulas/functions. Since it is complex issue, but, hopefully your desired feature would be supported in 10-15 days.

Thanks for your understanding and keep in touch.

Thank you.

6

Hi,

Any news/progress on this issue?

Thanks,

Timo

7

Hi Timo,

Hopefully, we can provide the fix in 1-2 days (before the end of this week or so).


Thank you.

8

Hi,

Please try the fix as we have supported to read external links from the template file and calculate them.

Thank you.

9

Hi,

The new version doesn’t seem to fix the problem I described. It is the setting of a formula to an external workbook that doesn’t work.

As can be seen in the sample project I attached in my first post, when I set this formula <=‘C:\BOOK1.XLS’!SomeNamedRange> using Aspose it will show up in MS-Excel as <=’‘C:\BOOK1.XLS’!SomeNamedRange’>. Aspose adds a single quote after the equal sign and at the end of the formula. This will make the formula unreadable for MS-Excel.

If the problem isn’t clear please ask, I will try to elaborate more.

Thanks,

Timo

10

Hi Timo,

Thanks for pointing it out.

Yes, we find Aspose.Cells for .NET puts an extra quote after the equal sign and at the end of the formula.

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

Thank you.

11

Hi,

Please try the attached version. We have supported to set the external name in the formula. And please change the formula as the following:

cells[“A1”].Formula = “=‘C:\book1.xls’!SomeNamedRange”;
cells[“A2”].Formula = “=’[C:\book1.xls]’!SomeNamedRange”;


Thank you.

12

Thanks for providing this fix. Now we can generate approx. 250 Excel-sheets in 45 min in stead of 6 hours (using MS-Excel)!