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

Question

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

If I add a macro to the Excel worksheet, this seems to be discarded when I close the worksheet and re-open it.

However, it is possible to have a chart with macros, because I also have some Word documents & PowerPoint presentations that I created in Office 2003, which had embedded Excel charts with macros. When I converted those to Office 2010, they look just like "normal" Office 2010 charts, but the macros are preserved.

It looks to me like the embedded chart that gets created when you choose Insert -> Chart is in the "pptx" format rather than the "pptm" format, and so macros are not saved.

(If I query ActiveWorkbook.FileFormat, I get "51", which is "Open Xml Spreadsheet"; this is indeed "pptx", as opposed to "Open Xml Spreadsheet with Macros", which is "52").

How can I insert a chart with the "pptm" behavior? Or change the behavior of an existing chart?

---

NOTE: I do not want to insert a chart "object", since this means that the chart is not editable within the host application (it merely inserts an Excel chart that you need to "open" in order to edit it).

---

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

1. Create a new document in Word (or PowerPoint).
2. Save the document as a 97-2003 document (which forces it into compatibility mode).
3. Insert a chart using Insert->Object->Microsoft Excel Chart. This embeds an old-style Excel chart.
4. Open the embedded chart by right-click->Open, and add a macro.
5. Close the chart, then choose File->Info and use the Compatibility Mode "Convert" button to convert the document into an Office 2010 document.

Now you have a "native" Word or PowerPoint 2010 chart, which you can edit directly in Word or PowerPoint. If you click on the chart and choose Chart Tools->Design->Edit Data, then the Excel worksheet that opens has the macros you created in step 4. Success!

Now, does anyone have a simpler way?

Concepcion

You may simply be able to migrate the macro to the Word/PPT code modules, and manipulate the ChartObject thusly: https://mcmap.net/q/1915734/-how-to-create-and-format-various-charts-in-powerpoint-based-on-excel-data. I normally don't write to the chart's ChartData worksheet, but that could be done, if needed. There are some quirks working with ChartObjects in PPT/DOC but mostly it will be familiar to you, if you are familiar with Excel VBA. – Rag

@DavidZemens: yes, I could do that, but with dozens of charts in a document it gets very very messy. It's much more practical for my purposes to keep the code associated with the chart (data) that it manipulates. – Concepcion

It's a bit ugly but you could embed an Excel spreadsheet instead (Insert -> Object -> From File) as that'll retain the macros. I guess it depends what the macros are doing and how you trigger them. – Dentil

@CuberChase: I need the file to be self-contained, so a link to an external workbook would not suit. Also, the chart is no longer editable within the host application, which is the behavior you get with a "native" chart. – Concepcion

You may have seen this already... but still worth a shot – Hunley

Answer 1

Now that the bounty has passed (with no better answers), I'll add the partial answer from my question, in order to be able to close the question.

---

1. Create a new document in Word (or PowerPoint).
2. Save the document as a 97-2003 document (which forces it into compatibility mode).
3. Insert a chart using Insert->Object->Microsoft Excel Chart. This embeds an old-style Excel chart.
4. Open the embedded chart by right-click->Open, and add a macro.
5. Close the chart, then choose File->Info and use the Compatibility Mode "Convert" button to convert the document into an Office 2010 document.

Now you have a "native" Word or PowerPoint 2010 chart, which you can edit directly in Word or PowerPoint. If you click on the chart and choose Chart Tools->Design->Edit Data, then the Excel worksheet that opens has the macros you created in step 4. Success!

I'm still interested in being able to do this more directly...

Concepcion

Answer 2

In my opinion you could achieve what you need in the following way:

(unfortunately, I can't present it with pictures and exact English commands as I'm using local-not English-version of Office)

• Choose Insert >> Object >> Object...
• In Object window take first page (like Create new)
• Search for Microsoft Excel Chart (or something similar) and press OK
• You will get Workbook with two sheets as presented on the picture below

(there are chart editing feature available on the Ribbon)

• Press Alt+F11 while you are in Chart edition like presented in the picture above. You will get IDE for Excel opened where you will find appropriate workbook.
• Add new module and macro there
• Now you could exit chart edition in Word and save document as *.docm
• After you will reopen it, go to Chart Edition (double click) >> Alt+F11 >> you will find you macro there saved within the workbook with *docm extension.

Saberio

unfortunately this method creates an embedded Excel workbook, rather than a native Word or PowerPoint chart. That's not what I'm trying to achieve. With this method, the chart is only editable after you activate it (by double-clicking on it, or by right-clicking and choosing "open"). – Concepcion

I think this is the option used in Office 2003 which you refer to. It keeps lot's of 'native chart' features of Office 2007/2010 like formatting. – Saberio

Let me explain what I mean: if I create a chart by choosing "Insert->Chart" from within Word or PowerPoint 2010, then the chart is actually a Word/PowerPoint chart, not an Excel chart. Both Word and PowerPoint have their own object model for these charts, which is similar to - but not identical to - the Excel version. And when you create charts like that, you can select the series and format it directly within Word or PowerPoint, rather than having to "activate" the chart to do it in Excel. That's the kind of chart I'm trying to create. – Concepcion

I think Microsoft idea was to get rid of this feature in new Office version as you have more options to code all elements of the Charts from Word/PP point of view. Therefore I don't think it's possible what your are exactly looking for. (if i'm wrong I will delete this comment :)). Of course they kept old options which was used in Office 2003 which is presented in my answer. There are some differences between these two chart types but lot's are similar. But if the goal is to have macro embedded than my proposal seems to be one of the possibilities. I look forward to knowing solution if exists. – Saberio

See my updated question; if you want to copy-paste that into an answer, then I don't mind giving you the points in return for the time you've spent trying to help. (Though if someone comes up with a better answer...) – Concepcion

@GaryMcGill, in my opinion bounty should be given for correct answer, not for the time spent :) I don't mind if I loose! I'll have a look into that again soon. – Saberio

@GaryMcGill, I made additional checks and I have no idea how to do it differently than presented above and described by you in your question's edition. To be honest I like the way you do it... – Saberio

Answer 3

The issue is with how the charts data is held within the application. Converting and originating are two different processes. Your data series is basically an array not an excel sheet. It just uses excel to display the data to you to edit. You need to keep your code in the module for powerpoint/word and access the chart via the shapes object if you do not want to embed a 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

Marva

Sorry, but I think this is completely wrong. The data series is not an array - it can contain formulae as well as macros, etc. See the last part of my question where I explain one way to achieve this. And as I explained in the comments, putting the code in the Word/PowerPoint is not the solution I'm looking for. – Concepcion