How to link an Excel range into a Word document

Aspose.Words Product Family
1

Hi,

I want to link an Excel range into a Word document.
The result should be similar to when I copy from Excel and paste into Word as a link, e.g. the field code should be
{ LINK Excel.Sheet.12 c:\temp\word\test.xlsx Sheet2!L2C2:L3C3 \a \f 5 \h * MERGEFORMAT }

I first tried using DocumentBuilder.InsertField, but it seems that it cannot do the job: I get an InvalidOperationException : Field code … is invalid or not supported. The Aspose.Words documentation says:
“There are some particular fields that exist in a Word document that are not imported into Aspose.Words as a collection of FieldXXX nodes. For instance, LINK field and INCLUDEPICTURE field are imported into Aspose.Words as a Shape object.”

Then I thought maybe I could do it using DocumentBuilder.InsertOleObject, however I do not see how to customize the field code.
Could you show me how to create such link fields programatically?

2

@lswe,

Thanks for your inquiry. The FieldLink class implements the LINK field. Following code example shows how to insert the LINK field. Hope this helps you.

Document doc = new Document(); 
DocumentBuilder builder = new DocumentBuilder(doc);

FieldLink field = (FieldLink)builder.InsertField(FieldType.FieldLink, false);
field.SourceFullName = @"C:\temp\test.xlsx";
field.SourceItem = @"Sheet2!R2C3:R3C12";
field.ProgId = "Excel.Sheet.12";
field.AutoUpdate = true;

field.Update();
                
doc.Save(MyDir + "output.docx");
3

Thank you for your answer. With your code I have been able to create a LINK field for an Excel range.
However, it does not always work.

When I open the document after creating a LINK with Aspose, Word will display the range only if those three requirements are met:

  • I have to answer yes to “Do you want to update the fields?”, and
  • The Excel file has to exist at the specified path, and
  • The language used by Office has to match the one that was used for creating the R1C1-style range in the LINK field. (E.g. in French, “R1C1” needs to be “L1C1” instead)

When I open the document after creating a LINK with Word, Word will always display the range correctly, regardless of whether I update it and regardless of the language.

I want to create a LINK that works in all cases, like the one Word generates. What should I do?

4

@lswe,

Thanks for your inquiry.

In this case, we suggest you please do not use FieldLink.AutoUpdate property.

Please make sure that you are executing the code under French culture.

If you still face problem, please manually create your expected Word document using Microsoft Word and attach it here for our reference. We will investigate how you want your final Word output be generated like. We will then provide you more information on this along with code.

5

Here is an example for you to investigate.
In attachment you will find two Word documents, each containing a LINK field to an xslm file:

  • aspose.docx was created with Aspose.
  • word.docx was created with Word.

When opening word.docx, the linked table is displayed.
When opening aspose.docx, the linked table is not displayed.

attachments.zip (16.0 KB)

6

@lswe,

Thanks for sharing the document. We need the “test.xlsm” for testing. Please ZIP and attach this document. Thanks for your cooperation.

7

Here it is. However, test.xlsm should not be needed – Word must display the linked table even when test.xlsm is not found.
test.zip (8.0 KB)

8

@lswe,

Thanks for sharing the documents. We have tested the scenario and have managed to reproduce the same issue at our side. For the sake of correction, we have logged this problem in our issue tracking system as WORDSNET-16226. You will be notified via this forum thread once this issue is resolved.

We apologize for your inconvenience.

9

Hello again,

I’d like to know when you plan to correct this problem. We are waiting for a solution.

10

@lswe,

Thanks for your inquiry. We regret to share with you that this issue has been postponed. The underlying problem here is complex and involves many internal tests as well. We will inform you via this forum thread once there are any further developments.

We apologize for your inconvenience.