Embedding Word Document in Excel Worksheet

Add-in Express™ Support Service
That's what is more important than anything else

Embedding Word Document in Excel Worksheet
Problem accessing embedded document 
Leon H H




Posts: 55
 Joined: 2010-04-06
Below is the code I came up with. It embeds the document (I can see it in the sheet), but in the wrong sheet and also after that I am unable to access it - simply because I am unable to find it... What do I do wrong? See comments in the body of code:

[CODE] private void AuthRibbonBtn_OnClick(object sender, IRibbonControl control, bool pressed)
{
using (Log.VerboseCall())
{
Excel.Range esidCol = null;

try
{
// Add new sheet to the Resulting workbook, after all the loaded sheets
AuthWorksheet = (Excel.Worksheet)ResultWorkbook.Sheets.Add(After: ResultWorkbook.Sheets[ResultWorkbook.Sheets.Count]);
AuthWorksheet.Name = "Authorization Letter";


string resultWorkbookPath2 = System.IO.Path.Combine(SelectedFolderPath, "ResultWorkbook.xlsx");
SaveWorkbook(resultWorkbookPath2);

WordFilePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData),
@"ConnectedSolar\AuthorizationLetter\AuthorizationLetterTemplate.docx");

try
{
// Activate the new worksheet
AuthWorksheet.Activate();

OLEObjects oleObjects = (Microsoft.Office.Interop.Excel.OLEObjects)
AuthWorksheet.OLEObjects(Type.Missing);

// PROBLEM #1:
// The document gets embedded, but not in the AuthWorksheet but in another
// worksheet I added to the workbook before that...
OLEObject oleObj = oleObjects.Add(
Type.Missing, // ClassType
WordFilePath, // Filename
false, // Link
false, // DisplayAsIcon
Type.Missing, // IconFileName
Type.Missing, // IconIndex
Type.Missing, // IconLabel
Type.Missing, // Left
Type.Missing, // Top
Type.Missing, // Width
Type.Missing // Height
);



// Activate the shape
oleObj.Activate();

// PROBLEM 2:
// Unable to retrieve the full path to the embedded document
// The bottom line is that I am unable to access the document and insert in it the
// values from Excel Worksheet.
string sourceFullName = (string)oleObj.Object.GetType().InvokeMember("FullName", System.Reflection.BindingFlags.GetProperty, null, oleObj.Object, null);
string sourceFileName = System.IO.Path.GetFileName(sourceFullName);
string sourceFilePath = System.IO.Path.Combine(SelectedFolderPath, sourceFileName);

// Get the Word document object
Word.Application wordApp = new Word.Application();
Word.Document wordDoc = wordApp.Documents.Open(sourceFilePath);

if (wordDoc != null )
{
PopulateTableInDocument(wordDoc);
}

string filePath = @"C:\tmp\try1.docx";
wordDoc.SaveAs2(filePath, Word.WdSaveFormat.wdFormatDocument, Word.WdCompatibilityMode.wdCurrent);

// Save the Excel workbook
string resultWorkbookPath = System.IO.Path.Combine(SelectedFolderPath, "ResultWorkbook.xlsx");
SaveWorkbook(resultWorkbookPath);

Log.Verbose("Word document embedded successfully.");
}
catch (Exception ex)
{
Log.Verbose(ex);
}
}
catch (Exception ex)
{
Log.Verbose(ex);
}
finally
{
if (esidCol != null) Marshal.ReleaseComObject(esidCol);
}
}
}
Posted 02 Jun, 2023 14:27:55 Top
Andrei Smolin


Add-in Express team


Posts: 19122
 Joined: 2006-05-11
Hello Leon,

Leon H H writes:
It embeds the document (I can see it in the sheet), but in the wrong sheet


This works for me. It may not work for you because you leave a great number of COM object non-released. I suggest that you walk through your code and release all COM objects carefully. Also, when dealing with Excel (and Word!) you should constantly check the list of processes on the Details tab of the Task Manager window: EXCEL.EXE or WINWORD.EXE hanging in the list is a typical indication of a COM object(s) left non-released.

Leon H H writes:
also after that I am unable to access it - simply because I am unable to find it


You need to debug your code. In my case the embedded document has the name "Document in ResultWorkbook2.xlsx"; naturally, you won't be able to find it anywhere. Check whether Excel.OLEObject.SourceName is what you are looking for. If not, consider storing that info in the associated cell or on a worksheet having Worksheet.Visible set to xlVeryHidden to prevent the user to make the sheet visible via the Excel UI.

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 05 Jun, 2023 04:38:34 Top