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 IfPublic 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