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.