We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

SetExternalLinksInFormulas Error

SetExternalLinksInFormulas Demo in the Github got some issue.
I’ve tried to use

cells["A1"].Formula = "=SUM('[" + outputDir + "ExternalData.xlsx]Sheet1'!A2, '[" + outputDir + "ExternalData.xlsx]Sheet1'!A4)";

and the output file is shown like

"=SUM('file:///E:\Aspose.Cells-for-.NET-master\Examples\Data\02_OutputDirectory\[ExternalData.xlsx]Sheet1'!A2,'file:///E:\Aspose.Cells-for-.NET-master\Examples\Data\02_OutputDirectory\[ExternalData.xlsx]Sheet1'!A4)"

the value of Cell is always #REF

@DamianRice,

Which Aspose.Cells for .NET demo you are using? Do you need to get resultant value of the formula involving external links or external source at runtime via the APIs? You may not get calculated results against external referenced formulas.

Thanks for your reply.
Yeah I do need to get resultant value.
It always displayed like #REF .
Should I call the workbook.CalculateFormula() to get resultant value?
Is there any way to make only one cell to be recalculated ?
Hope you could tell me the right way.
Many Thanks

@DamianRice
We create “ExternalData.xlsx” file in “D:\Filetemp”, then save “dest.xlsx” with the following codes:
string dir = @“D:\Filetemp”;
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
cells[“A1”].Formula = “=SUM(’[” + dir + “ExternalData.xlsx]Sheet1’!A2, '[” + dir + “ExternalData.xlsx]Sheet1’!A4)”;
workbook.Save(dir + “dest.xlsx”);

Everything works fine.
dest.zip (6.7 KB)

I tried several times with the same code and it doesn’t work.
And, there seem to be some issues in the demo you’ve posted.
0> There are some Chinese characters like “ ” ‘ ’. I corrected it in my demo.
1> dir+ “ExternalData.xlsx]… is different from Path.Combine()
the demo above may output the path like “D:\FiletempExternalData.xlsx”
Path.Combine() could add the separator and final output is like “D:\Filetemp\ExternalData.xlsx”. I tried two different ways and didn’t work. :frowning_face:

Probably there is something wrong with my env or the version of Aspose.Cells?
I’m not quite sure about that.
Thank you guys so much for helping. :relaxed:

Btw I am using Aspose.Cells. 22.2.0.0 and .Net472 x64

@DamianRice,

I tested the scenario/case a bit. First, I created “Filetemp” folder on D drive. Then, I created “ExternalData.xlsx” file (it has “10” and “20” in A2 and A4 cells of Sheet1) in MS Excel manually to save the file in “D:\Filetemp” folder. Now I used the following sample code to generate the “dest.xlsx” in the folder.
e.g.
Sample code:

string dir = @"D:\Filetemp\";
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
cells["A1"].Formula = "= SUM('[" +dir + "ExternalData.xlsx]Sheet1'!A2, '[" + dir + "ExternalData.xlsx]Sheet1'!A4)";
workbook.Save(dir + "dest.xlsx");

Now I opened the file “dest.xlsx” into MS Excel (e.g. 2016) manually. I found there is a security warning and rightly so. I need to click “Enable Content” or Update to trust the source. Now I can see A1 cell has calculated value, i.e., “30”.

:sob: I tried this and saw the Notification about Enable Conent. It didn’t work anyway.
But, If I open the “External.xlsx” firstly, then

ws.Cells[“A1”].Formula = “=SUM(’[External.xlsx]Sheet1’!A1)”;

The Final Result Excel file could display the resultant value of Excel.xlsx Sheet1 A1.

Maybe it caused by the security settings of Excel Application?

@DamianRice,

Yes, may be but I am not entirely sure about it. It works fine on my end. By the way, which MS Excel version you are using? I tested using MS Excel 2007, 2013 and 2016. All three versions show the calculated value in A1 cell of the worksheet (of output file) fine (after Enable Content).

I am using Excel 365 for Family. Probably the lower version I should use?

@DamianRice
I am using Excel 365 in my machine too.If we open “dest.xlsx” in the previous post, an warning is thown as following:
7.png
If “update” is clicked , everything works fine.

It’s weird though I tried several times and it didn’t work anyway.
The only way I could use is to open the External Link file firstly.

I’m not sure am I the only one facing this problem? :cry:

@DamianRice,

We are sorry but we tried on different machines (see my post for reference where I mentioned steps and everything which I did to evaluate the issue but it works fine) and could not find the issue.

No worries @Amjad_Sahi.
It’s most likely some problem with my machine.

However, I’ve seen a post before that is about this issue.
It said, in version 19.12 there is a new API called Cell.SetFormula(string formula, FormulaParseOptions, object value) fixed this issue.

I tried 18.14 before and didn’t see the difference between 18.14 and 19.12, they all have the same issue (at least in my machine :cry: )

Anyway, I found a solution to avoid this problem.

Thanks for your help @Amjad_Sahi @simon.zhao :relaxed:

@DamianRice,

You are welcome and good to know that you have sorted it out now.