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

Free Support Forum - aspose.com

Formulas are not reflecting the correct data in the external workbook links

I have a workbook with a link to an external workbook. There are formulas in the workbook as seen from aspose that references the linked workbook as follows:

=IF(E2<>0,VLOOKUP(H2,'C:\Program Files\ActiveERP\Web\[webPriceList.xls]InternalPriceList'!$D$5:$Y$65536,$B$1,FALSE),0)

With the correct data in E2 & H2 this formula returns the proper numeric results in excel, however it always returns 0 in Aspose.Cells. The VLOOKUP function does not seem to work with external links, however, according to the documentation, Aspose.Cells supports external links.

The following is my code that updates the external links and does the calculations. Note that oWkb is an instance of MyWkbk

'This is the code I use to call the Procedure below

If oWkb.HasExernalLinks Then
oWkb.UpdateLinks(sError)
End If

Public Class MyWkbk Inherits Aspose.Cells.Workbook

...
Public Sub UpdateLinks(ByRef sError As String)
Try
If Me.Worksheets.ExternalLinks.Count > 0 Then
Dim ExWbs As Workbook() = Nothing

For i As Integer = 0 To Me.Worksheets.ExternalLinks.Count - 1
Dim LinkCounter As Integer
Dim e As ExternalLink = Me.Worksheets.ExternalLinks.Item(i)

If Not e Is Nothing Then
Try
If e.DataSource.Trim.Length > 0 Then
Dim wbPath As String = e.DataSource

If System.IO.File.Exists(wbPath) Then
Dim wb As Workbook = New Workbook

wb.Open(wbPath)

ReDim Preserve ExWbs(LinkCounter)

ExWbs(LinkCounter) = wb

If wb.HasExernalLinks Then
wb.UpdateLinks(sError)
End If

wb.CalculateFormula()

LinkCounter += 1
Else
sError = "Linked File: '" & wbPath & "' does not exist"
End If
Else
sError = "Blank linked file: '" & e.DataSource.Trim &
"' was located. Please edit or remove all invalid links from your workbook"
End If
Catch ex As Exception
sError = ex.Message
End Try
End If
Next

If sError.Length = 0 Then
Me.UpdateLinkedDataSource(ExWbs)
Me.CalculateFormula()
End If
End If
Catch ex As Exception
sError = ex.Message
End Try
End Sub


...
End Class

Hi,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version:
Aspose.Cells
for .NET v7.1.2.4
and let us know your feedback.

Your problem should be fixed in this version.

If the problem still occurs, then please provide us your runnable code/project.

I was unable to run your code because of some compile errors.

Please also provide us your source xls/xlsx files if there are any.