How to add a macro to a Word 2010 or PowerPoint 2010 chart?

Question

In PowerPoint 2010 or Word 2010, when I select Insert → Chart, it creates a new chart with an Excel worksheet for the data.

If I add a macro to the Excel worksheet, it seems to be undone when I close the worksheet and open it again.

However, it is possible to have a chart with macros because I also have Word documents and PowerPoint presentations that I created in Office 2003 that had Excel built-in graphs with macros. When I converted them to Office 2010, they look just like the “regular” Office 2010 graphics, but the macros are saved.

It seems to me that an inline chart is created when you select Insert → Chart in the "pptx" format, and not in the "pptm" format, and therefore macros are not saved.

(If I request ActiveWorkbook.FileFormat, I get "51", that is, "Open Xml Spreadsheet", it really is "pptx", unlike "Open Xml Spreadsheet with Macros", which is "52").

How to insert a diagram with the behavior of "pptm"? Or change the behavior of an existing chart?

---

NOTE. I don’t want to insert the “object” diagram, since this means that the diagram is not being edited in the host application (it just inserts the Excel diagram that needs to be “opened” to be edited).

---

Let me give you a start ... I can achieve what I want with the following method:

• Create a new document in Word (or PowerPoint).
• Save the document as document 97-2003 (which forces it to work in compatibility mode).
• Insert a chart using Insert-> Object-> Microsoft Excel Chart. It embeds an old-style Excel chart.
• Open the built-in diagram with the right mouse button-> Open and add a macro.
• Close the chart, then select "File-> Information" and use the "Convert" button in compatibility mode to convert the document to an Office 2010 document.

Now you have a “native” Word or PowerPoint 2010 chart that you can edit directly in Word or PowerPoint. If you click on the chart and select "Chart Tools" → "Design → Change Data", will the Excel sheet that you created in step 4 open. Success!

Does anyone now have an easier way?

Answer 1

Now that the generosity has passed (without better answers), I will add a partial answer from my question in order to be able to close the question.

---

• Create a new document in Word (or PowerPoint).
• Save the document as document 97-2003 (which brings it into compatibility mode).
• Insert a chart using Insert-> Object-> Microsoft Excel Chart. It embeds an old-style Excel chart.
• Open the inline chart by right-clicking -> Open, and add the macro.
• Close the chart, then select "File-> Information" and the Compatibility Mode "Convert" to convert the document to an Office 2010 Document.

Now you have a “native” Word or PowerPoint 2010 chart that you can edit directly in Word or PowerPoint. If you click on the chart and select "Chart Tools" → "Design → Change Data", will the Excel sheet that you created in step 4 open. Success!

I am still interested in the opportunity to do this more directly ...

Answer 2

In my opinion, you could achieve what you need as follows:

(unfortunately, I can not imagine it with pictures and exact English commands, since I use the local, not English version of Office)

• Choose Insert → Object → Object ...
• In the Object window, select the first page (for example, Create New )
• Find the Microsoft Excel chart (or something similar) and click OK
• You will receive a Workbook with two sheets , as shown in the image below.

(it is possible to edit the chart on the tape)

• Press Alt + F11 while you are in the chart editor, as shown in the figure above. You will open the IDE for Excel, where you will find the corresponding workbook.
• Add new module and macro
• Now you can exit the chart diagram in Word and save the document as *.docm
• After you open it , go to Chart Edition (double click) -> Alt + F11 -> you will find the macro saved in the book, with the extension *docm .

Answer 3

The problem is how the chart data is stored in the application. Transformation and creation are two different processes. Your data series is basically an array that is not an excel sheet. It just uses excel to display the data for editing. You need to save your code in the module for powerpoint / word and access the diagram through the shapes object if you do not want to insert an excel sheet.

 Sub GetChartName() Dim sSlide As Slide Dim cChart As Chart Dim sShape As Shape Set sSlide = PowerPoint.ActivePresentation.Slides(1) For Each sShape In sSlide.Shapes If sShape.HasChart Then Set cChart = sShape.Chart MsgBox cChart.Name Set cChart = Nothing End If Next Set sSlide = Nothing End Sub