Link Excel Worksheet in a Word document

Question

question

ApsSanj-3718 asked · Feb 22 at 05:48 AM

Link Excel Worksheet in a Word document

What I wanted to achieve :

Implement a function in C# which executes similar to the Word object insertion as a link and attach a selected excel file into the word document with 'link to file', so that the changes to the excel source file is reflected in the Word document).

Word Insert > Object( in Text button group) > Create from File

What I tried :

I am using Office 365.

Get the UsedRange.AddressLocal from a selected worksheet of the excel file, and get cell range from it. Then use the Word.Range.PasteSpecial method.

 string addressLocal = worksheet.UsedRange.AddressLocal;
 if (addressLocal.Length > 0)
 {
    cellRange = addressLocal.Replace("$", "");         
 }
 Excel.Range range = this.worksheet.Range[cellRange];
 range.Copy();

Get the range of the current selection.

wordRange = wordApp.Selection.Range;

Paste the copied range to the Word document.

 wordRange.PasteSpecial(
      Link: true,
      DataType: Word.WdPasteDataType.wdPasteOLEObject,
      Placement: Word.WdOLEPlacement.wdInLine,
      DisplayAsIcon: false
 );

But this gives an error when we try double click on the inserted excel.

I have went through many articles in web related to this error, but none of them fixed my issue.

But when I change the copying excel cell range as below and pasting it doesn't give errors when double click and open the inserted excel.

 string addressLocal = worksheet.UsedRange.AddressLocal;
 if (addressLocal.Length > 0)
 {
     cellRange = addressLocal.Replace("$", "");
     // If cellRange is "A1:E6", below code make it as "A1:E5"
     string a = cellRange.Substring(0, cellRange.Length - 1);
     int b = int.Parse(cellRange.Substring(cellRange.Length - 1, 1)) - 1;
     cellRange  = a+ b.ToString();
 }
 Excel.Range range = this.worksheet.Range[cellRange];
 range.Copy();

Is my approach for implementing the linking excel to word is incorrect? If so, could anybody please let me know what should I do to implement some functionality similar to word Excel object linking?

If my approach is correct, what should I do to insert the exact used range of the excel worksheet to the word?

Please help me with this.

Thank you in advance.

dotnet-csharp office-addins-dev

2kwaf.png (28.4 KiB)

excellink.png (16.7 KiB)

· 8

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 · Feb 22 at 08:31 AM

What happens if you copy the Excel range using a single — worksheet.UsedRange.Copy() — then paste to Word?

0 ·

ApsSanj-3718 Viorel-1 · Feb 22 at 10:40 AM

@Viorel-1
It throws below error and not inserting the excel to word.
"Word cannot obtain the data for the {00020830-0000-0000-C000-000000000046} link."

0 ·

Viorel-1 ApsSanj-3718 · Feb 22 at 11:27 AM

Does it work if you select and copy the same range manually, then paste to Word using Paste Special command, specifying “Paste link”, “As Excel”?

0 ·

Show more comments

Answer 1 · 2021-03-02T02:20:12.717Z

DanielZhang-MSFT answered · Mar 02 at 02:20 AM

Hi ApsSanj-3718,
You can try to use Range.PasteExcelTable method to embed an excel-file in a word document.
Here is my test code you can refer to.

 using Excel = Microsoft.Office.Interop.Excel;
 using Microsoft.Office.Interop.Word;
 using Application = Microsoft.Office.Interop.Word.Application;
    
 var path =  @"C:\Users\Desktop\tt.xlsx";         
 Excel._Application excel = new Excel.Application();
 var template = excel.Workbooks.Add(path);
 var worksheet = (Excel.Worksheet)template.Worksheets["Sheet1"];
 var table = (Excel.Range)worksheet.Range["A1:A5"];
 table.Copy();
 Application ap = new Application();
 Document document = ap.Documents.Open(@"C:\Users\Desktop\tt.docx");
 document.Bookmarks["test"].Range.PasteExcelTable(false, true, false);

Best Regards,
Daniel Zhang

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2 ·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ApsSanj-3718 · Mar 02 at 09:38 AM

Thank you for your reply.
But I have already tried this way and this does not do what I want. I need to link an excel file to word, not to embed it. Also user should be able to open the linked excel by double clicking the inserted excel table.

I tried changing this line from your code to insert the excel as link, But it also didn't work.
document.Bookmarks["test"].Range.PasteExcelTable(true, true, false);
When right click on the inserted table and click 'Linked worksheet object> Edit link/ Open link', it gives the same error as mentioned in the issue description.

0 ·

DanielZhang-MSFT ApsSanj-3718 · Mar 03 at 09:19 AM

Hi @ApsSanj-3718,
Sorry for my misunderstanding,
You can try to use the Fields.Add method.
There is a detailed explanation and code example in "Adding Linking in Word Tables Programmatically" section of this document you can refer to.
Best Regards,
Daniel Zhang

1 ·

question