none
Linking Excel tables in Word RRS feed

  • Question

  • I am using Office 2010. I have been linking Excel ranges into Word via VBA. These ranges are inserted as static tables or linked table. The linking was OLE linking so that users can refresh these whenever they need to. The OLE linking sometimes leeds to crashes and is not much stable.

    Can someone help me understand is there a better way of doing this without OLE. I am hoping that the Open XML format of Office 2010 could provide a better option to link Excel tables in Word without OLE but I can still refresh these tables whenever necessary.

    Thanks.

    Thursday, March 3, 2011 10:49 AM

Answers

  • Just a few thoughts...

    The problem with working with the Open XML format is that you don't really get to work with the XML directly /while you have the document open in Word/ (even if you are using VSTO, AFAIK). In other words, everything still has to go via the Word Object Model in one way or another, although you can inject Open XML into your document in a limited way.

    Which I suppose leaves the question as to whether there is a more reliable way to update an existing link, perhaps by providing your own code that would replace the code built into Word. And that, I think depends at least partly on what you need to achieve, and what is going wrong.

    For example, a normal link update via OLE would result in Excel starting, opening the linked workbook, and returning the linked sheet/range to Word, in the format requested by Word.

    If, for example, the sheet/range is displayed in Word as a picture, I think it would be quite hard to achieve the same thing with your own code, because the image of the sheet/range is provided by Excel. So /your/ code would have to start Excel, open the linked file, copy/paste the relevant range, then paste the result into Word. In that case, if the problem is that there is some difficulty automating Excel, using your own code probably isn't going to improve anything. But if the problem is that /Word/ is having difficulty automating Excel, then maybe doing it in your own code would be better and allow you more control. In that case, all I can suggest is that you start with the idea that instead of your users inserting a normal link into a document, you provide them with a structure that you define. FOr example, it might be a rich text control control with enough information in the Control's tag to allow you to identify the workbook and range that you need to re-insert.

    But if it would be enough to get the data, format it yourself, and insert it yourself, then you might not need Excel at all, as long as you can create software that can read and interpret an Excel workbook file. For the old .xls format, that might be quite hard. For .xlsx/.xlsm, you might be able to use .NET and the Open XML to do it. But even then, I am not sure what happens if you need to get data from an /open/ Excel file - OLE can do that because it communicates with the Excel application, but AFAIK Open XML can only get data from a closed .xlsx/.xlsm file.

     

     

     

     

     


    Peter Jamieson
    Thursday, March 3, 2011 10:18 PM