In our previous posts we have seen how to Embedd an Word Document in Excel Object . Now let us try to read Excel spreadsheet embedded in Word document.
Excel Object Libarary ReferenceYou need to add a reference to the Excel Object Libary as shown above from Tools --> References from Visual Basic Editor (VBE)
The code loops through the available InlineShapes and activates them if they are Excel Spreadsheet. Then it is assigned to an Excel workbook object, which can be programatically handled.
Sub Edit_Embedded_Excel_Objects()
Dim oWB As Excel.Workbook
Dim oIShape As InlineShape
For Each oIShape In ActiveDocument.InlineShapes
If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then
oIShape.OLEFormat.Activate
Set oWB = oIShape.OLEFormat.Object
oWB.Sheets(1).Range("A1").Value = "ProdID"
End If
Next oIShape
End Sub
The code edits the value of the cell as shown below:
Edited Embedded Object using VBASee how other Embedded objects are programmed
Posts
I did not understand how to insert the reference. Would you kindly be more explicit? Thank you!!
ReplyDeleteHi Pansulat
ReplyDeleteYou can do that from Tools-->References-->Select the appropriate references from the dialog
Cheers
Shasur
Are we talking about Word 2010 and/or Visual Studio 2010? The closest I get to what you say is, in Visual Studio, the menu Project>Add Reference... And then I can find a Microsoft Excel 14.0 Object Library option, but your code doesn't work in that case because one cannot access the collection of InlineShapes. Do you have further suggestions? Thank you!
ReplyDeleteThe reference needs to be added from Word 2010 --> Visual Basic Editor (Alt + f11). I have added the Menu to the post for your references
ReplyDeleteFantastic! Thank you.
ReplyDeleteI am trying to read embedded excel using c# and everything works fine but the call to activate() function makes the excel visible which is not desired.I ran the code by commenting the call to activate() function but then it gave exception of improper typecast.thanks in advance..
ReplyDeleteMany thanks! )) This morning I spent 3 hours searching and trying to do it, finding not a tiny hint - and at least! So simple and working just perfect.
ReplyDeleteCan we read without activate or display worksheet
ReplyDeleteCan you please explain more about OLEFormat? Keep up the great work!
ReplyDelete