Friday, August 06, 2010

How to Read Excel Sheet embedded in Word Document using VBA

How to edit Embedded Objects (Excel Workbook) using Word VBA

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.



You 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:


See how other Embedded objects are programmed

9 comments:

  1. I did not understand how to insert the reference. Would you kindly be more explicit? Thank you!!

    ReplyDelete
  2. Hi Pansulat

    You can do that from Tools-->References-->Select the appropriate references from the dialog

    Cheers
    Shasur

    ReplyDelete
  3. 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!

    ReplyDelete
  4. The reference needs to be added from Word 2010 --> Visual Basic Editor (Alt + f11). I have added the Menu to the post for your references

    ReplyDelete
  5. Fantastic! Thank you.

    ReplyDelete
  6. madhur8:17 AM

    I 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..

    ReplyDelete
  7. Anonymous1:46 AM

    Many 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.

    ReplyDelete
  8. Can we read without activate or display worksheet

    ReplyDelete
  9. Can you please explain more about OLEFormat? Keep up the great work!

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.