locked
Converting Embedded OLE Spreadsheet From Word To a Word Table RRS feed

  • Question

  •  

    I have a word document with an embedded EXCEL spreadsheet.  I would like to convert the visible EXCEL range and paste it back as a table in Microsoft word.  If I try to Open the excel sheet in an EXCEL application window, I can't guaranteed that the sheet itself will display the corresponding area shown in my word document (the sheet can have other ranges that I do not wish to transform).  My question is how do I select only the area of the sheet that is displayed in the OLE picture in Microsoft Word.  Any technolology will do, however does anyone know of how (or if its possible) to accomplish this using an VBA Macro in Microsoft word.

     

     

    Thanks,

    Nathan Lacoff

    Wednesday, February 13, 2008 12:47 PM

Answers

  • Hi Nate,

     

    This you are a bit off-topic for this forum--which is meant for questions regarding Visual Studio Tools For Office.  VSTO development occurs in C# and/or VB.Net so if you want Vba help, you will be best off posting in the Office newsgroup: http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.office.developer.vba&lang=en&cr=US .

     

    It should be possible to do what you want.  InlineShape.OLEFormat.Object should get you an Excel.Workbook object.  So if you add a reference to the Excel object model, you should be able to make that assignment.  From there it is just a matter of using the respective object models to pull the contents out of the embedded spreadsheet and construct a table. 

     

    The one thing I'm not sure about is whether you can actually delete the embedded object.  InlineShape has a Delete method on it, but I know it doesn't always work.

     

    Hopefully that will get you pointed in the right direction.  As I said, if you need help with the Vba syntax, you'll want to follow up with the office developer newsgroups.

     

    Sincerely,

     

    Geoff Darst

    Microsoft VSTO Team

     

    Wednesday, February 13, 2008 3:30 PM
    Answerer

All replies

  • Hi Nate,

     

    This you are a bit off-topic for this forum--which is meant for questions regarding Visual Studio Tools For Office.  VSTO development occurs in C# and/or VB.Net so if you want Vba help, you will be best off posting in the Office newsgroup: http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.office.developer.vba&lang=en&cr=US .

     

    It should be possible to do what you want.  InlineShape.OLEFormat.Object should get you an Excel.Workbook object.  So if you add a reference to the Excel object model, you should be able to make that assignment.  From there it is just a matter of using the respective object models to pull the contents out of the embedded spreadsheet and construct a table. 

     

    The one thing I'm not sure about is whether you can actually delete the embedded object.  InlineShape has a Delete method on it, but I know it doesn't always work.

     

    Hopefully that will get you pointed in the right direction.  As I said, if you need help with the Vba syntax, you'll want to follow up with the office developer newsgroups.

     

    Sincerely,

     

    Geoff Darst

    Microsoft VSTO Team

     

    Wednesday, February 13, 2008 3:30 PM
    Answerer
  •  

    Geoff,

    Thanks.  C#/Vs 2008 would work too.  The issue with the VBA solution is that if you activate the EXCEL workbook, you are not guaranteed to get the selected range that is visible in the word document.  I am trying to find out if there is anything contained within the OLE object in the word document that is either saves the associated EXCEL range or a pointer to a range.  I see that this is possibly related to the post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2389508&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2106198&SiteID=1.  The problem with these solutions is that it saves/gets the entire sheet rather than only the range displayed in the word document.

    Any help would be greatly appreciated.

     

    Thanks,

    Nathan

    Wednesday, February 13, 2008 4:58 PM
  •  NateLake wrote:

    Thanks.  C#/Vs 2008 would work too.  The issue with the VBA solution is that if you activate the EXCEL workbook, you are not guaranteed to get the selected range that is visible in the word document.  I am trying to find out if there is anything contained within the OLE object in the word document that is either saves the associated EXCEL range or a pointer to a range.  I see that this is possibly related to the post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2389508&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2106198&SiteID=1.  The problem with these solutions is that it saves/gets the entire sheet rather than only the range displayed in the word document.

    Any help would be greatly appreciated.

    As far as I know, this simply isn't possible. Just as it's not possible to change the size of the "window" displayed in the document. There's just no way to work with the "interface" between where the object "lives" and the application providing the service.

    Wednesday, February 13, 2008 7:44 PM
  • Hi Nathan,

     

    There aren't really any straightforward answers here because there aren't any hard requirements on the client when the in-place activation server is inactive.  To answer your initial question, there is nothing persisted with the embedded object that would indicate the visible range of cells.  Typically, the way this works is that the container will discover how much space the in place activation server needs by calling IOleObject::GetExtent.  To display the object when it is inactive, the client will typically call either IViewObject(2):Big Smileraw (in which case they will provide a device context with extents that match what the object wants) or they will call IDataObject::GetData in which case the object will return a metafile that they will then display.  The metafile will generally match the object's extents.  Of course the container can apply scaling by adjusting the rectangles accordingly.  When the object activates, the container must provide a window for the server to parent itself to. If the window is not the same size as the server's extents, then the server knows that scaling is being applied and must either scale accordingly or deactivate.

     

    So, I'm not sure what options you are going to have.  In general, what is visible when Excel is deactivated should match what is visible when you activate Excel (because Word does try to do the right thing here), so if you could activate Excel, get to the active pane and then call VisibleRange, you would probably be set.  However, I'm not sure if the Window/Pane objects are actually alive in the embedded object scenario. 

     

    The other option would be to get the extents of the Word InlineShape and then map those extents to Excel coordinates to determine what is visible.  This can be done (I did something similar to implement a feature in VSTO), but it requires a tremendous amount of work (and far more code than I can give you in a sample).  You have to account for the fact that row and column sizes can vary individually, and you also have to account for scaling.

     

    Sincerely,

     

    Geoff Darst

    Microsoft VSTO Team

    Wednesday, February 13, 2008 8:06 PM
    Answerer