none
Macro to update Object (excel chart) image cache RRS feed

  • Question

  • Hi,

    I have a couple of Excel charts embedded in a DOCM file that are not updating properly.  The image is wrong until you double click on it and then it regenerate the proper graph.   I tried opening and deleteing the .emf files associated with the charts to see if Word would re-generate them, but it didn't work, word gives you a red (X), until you double click on the place holder then the graphic is updated.

    Is using AutoOpen macro the right way doing this?  I'm assuming I can record a macro to see what the function name is to do an update.

    Thanks.

    Friday, December 17, 2010 2:38 PM

Answers

  • Hi Aram

    Gotcha (I think) :-)

    <<The chart are Object -> Excel Chart inserts.   They were created in Word 2007 (I'm using 2010, but that shouldn't make a difference in this case).>>

    Actually, it could, as Word 2010 better and more thoroughly integrates the charting engine introduced in Office 2007. I wouldn't rule this out if you encounter odd problems....

    So, just to make sure I'm on the same wave-length, your java program is opening up the Open XML document package, changing these values, and closing it?

    <<The solution -- manually -- is to double click on the chart...>>

    Right. The equivalent in the object model is to get the InlineShape.OleFormat and use a method on it such as Activate, DoVerb, Edit, Open. I don't know of any way to perform the update without actually "opening" the chart and the problem with this is that it's very difficult to do this such that the user won't need to CLOSE it.

    What you can try (but no guarantees):
    When the chart in a Word 2010 document is activated, it opens an Excel workbook alongside. You cannot prevent this. (I screamed during beta and was basically told tough luck, this is how it's been designed.)

    But you can get that workbook object: Excel.Workbook xlWB = objChart.ChartData.Workbook;

    And once you're done, you can close the workbook, which should restore the windows: xlWB.Close();


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by Bessie Zhao Friday, December 24, 2010 12:01 PM
    Friday, December 17, 2010 6:53 PM
    Moderator

All replies

  • Just as a followup, the following updates the chart, but obviously it converts it from an object to a non-editable shape. If at all possible I would like to keep it as an object so that the end user can edit or even just view the data as needed. For Each shape In Word.Application.ActiveDocument.InlineShapes shape.Select shape.ConvertToShape Next
    Friday, December 17, 2010 3:31 PM
  • Hi Aram

    Have you discussed this first in a forum that specializes in Word (end-user)? Something like this shouldn't require a macro...

    http://social.answers.microsoft.com/Forums/en-US/category/officeword

    It's a bit difficult to follow how these charts were created, originally (*.emf files?) and how you expect them to be updating.

    You also don't mention which version of Word is involved...


    Cindy Meister, VSTO/Word MVP
    Friday, December 17, 2010 4:01 PM
    Moderator

  • No, I but I will cross-post it there.

    The chart are Object -> Excel Chart inserts.   They were created in Word 2007 (I'm using 2010, but that shouldn't make a difference in this case).

    So assume Chart 1 has 5 rows and 2 columns, just doing a simple bar chart with values of: 1,2,3,4,5.

    I'm using a Java program to update the embedded Excel chart.  I'm changing the values to 20,19,18,17,16.

    Now word actually creatings an image file (used to be png - in 2007 they changed it to emf) of what the chart looks like with the values: 1,2,3,4,5 since it doesn't want to recalculate the bar graph every time.  It really only updates it when you double click on the object, launch excel and change the values.

    Since I'm not launching Excel word never realizes that the cached-image of what the chart should look like is no longer valid.  So even though the values have changed the original graph showing 1,2,3 ... shows in word.

    The solution -- manually -- is to double click on the chart -- then just escape back out again.  Word updates the cached image and the new chart with the values of 20,19.... is displayed.

    Now the question is programaticaly, how do I get word to update that cached image?  

    My current solution of converting the chart to a shape works, it updates the cached image but then the end user can no longer see the original values, or modify them.

    Please let me know if I confused any part this.

    Thanks.

    Friday, December 17, 2010 4:59 PM
  • Hi Aram

    Gotcha (I think) :-)

    <<The chart are Object -> Excel Chart inserts.   They were created in Word 2007 (I'm using 2010, but that shouldn't make a difference in this case).>>

    Actually, it could, as Word 2010 better and more thoroughly integrates the charting engine introduced in Office 2007. I wouldn't rule this out if you encounter odd problems....

    So, just to make sure I'm on the same wave-length, your java program is opening up the Open XML document package, changing these values, and closing it?

    <<The solution -- manually -- is to double click on the chart...>>

    Right. The equivalent in the object model is to get the InlineShape.OleFormat and use a method on it such as Activate, DoVerb, Edit, Open. I don't know of any way to perform the update without actually "opening" the chart and the problem with this is that it's very difficult to do this such that the user won't need to CLOSE it.

    What you can try (but no guarantees):
    When the chart in a Word 2010 document is activated, it opens an Excel workbook alongside. You cannot prevent this. (I screamed during beta and was basically told tough luck, this is how it's been designed.)

    But you can get that workbook object: Excel.Workbook xlWB = objChart.ChartData.Workbook;

    And once you're done, you can close the workbook, which should restore the windows: xlWB.Close();


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by Bessie Zhao Friday, December 24, 2010 12:01 PM
    Friday, December 17, 2010 6:53 PM
    Moderator