PowerPoint 2010 - programming charts with VBA

hindersaliva
hindersaliva used Ask the Experts™
on
Is there a good resource for learning how to dynamically create PowerPoint charts with VBA?

(I can get data from external database via ADO/SQL into a recordset. It's the next part of the journey in PowerPoint that I'm missing. Am pretty good at doing in Excel).

Is there an 'Excel like' worksheet object in PowerPoint?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John WilsonCEO PowerPoint Alchemy
Commented:
This should get you started

Sub CreateChart()
Dim oshp As Shape
Dim ochrt As Chart
Dim ochrtData As ChartData
Dim gWorkBook As Variant
Dim gWorkSheet As Variant

' Create the chart and set a reference to the chart data.
Set oshp = ActivePresentation.Slides(1).Shapes.AddChart(xlColumn)
Set ochrt = oshp.Chart
Set ochrtData = ochrt.ChartData

' Set the Workbook and Worksheet references.
Set gWorkBook = ochrtData.Workbook
Set gWorkSheet = gWorkBook.Worksheets(1)

 ' Add the data to the workbook.
 With gWorkSheet
.ListObjects("Table1").Resize gWorkSheet.Range("A1:B5")
.Range("Table1[[#Headers],[Series 1]]").Value = "Items"
.Range("a2").Value = "ITEM A"
.Range("a3").Value = "ITEM B"
.Range("a4").Value = "ITEM C"
.Range("a5").Value = "ITEM D"
.Range("b2").Value = "1000"
.Range("b3").Value = "2500"
.Range("b4").Value = "4000"
.Range("b5").Value = "3000"
End With

With ochrt.Axes(xlValue)
    .HasTitle = True
    .AxisTitle.Text = "Sales"
End With
 gWorkBook.Close
' Clean up the references.
Set gWorkSheet = Nothing
Set gWorkBook = Nothing
Set ochrtData = Nothing
Set ochrt = Nothing
Set oshp = Nothing

End Sub
Top Expert 2016
Commented:
Hi,

I've always found easier to create the charts in excel (use of formulas, macro recorder, etc...and then insert them in PPT

For reference

http://peltiertech.com/Excel/XL_PPT.html

Regards

Author

Commented:
Thanks JSRW. That's a good start.

Rgonzo, do you mean manually insert in PPT? There are some 200 charts that some person does each month, which we're trying to automate.  Ideally, hit one button and some 200 slides are created in a matter of minutes.
Do you mean, generate the VBA in Excel and run it off PPT?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
JSRW, that works brilliant. Feeling a sense of great power!

Rgonzo, my comment was a bit premature. I've checked the link you gave and it has a lot of the answers I'm looking for.

Wow. I'll be back with more specific questions.

Author

Commented:
It takes about 3 sec for a 1 series bar chart as above. 3 series takes 6 sec. Fine

The solution I'm after will be creating 150 charts on as many slides.
Q. would/might it be quicker if the chart elements are already in place, so that the only thing to make happen is to plug values into the cells? I'd like to try this out.

So, how would I modify John's code to address the 'worksheet attached to (say) Slide 1'?

Thanks.

Author

Commented:
Re-phrasing the question.

Say I place a column chart on Slide 4. Does slide 4 have a dedicated 'worksheet' attached to it?
If so, how do I address it?

Looking at John's code, it has an AddChart method. So, ok, I understand - the worksheet is the one for that slide that's just been added.
But if the Slide and the Chart already exist? (All I have to then do is to programmatically enter some values into specific cells. I think I can do all that .... if only I knew how to reference that 'worksheet' for Slide 4)
Thanks
CEO PowerPoint Alchemy
Commented:
The chart model in PPT is "not great"

You must activate the data to use it.

Sub chartSheet()
Dim oshp As Shape
Dim ocht As Chart
Set oshp = ActivePresentation.Slides(1).Shapes(3)
If Not oshp.HasChart Then Exit Sub
Set ocht = oshp.Chart
ocht.ChartData.Activate
ocht.ChartData.Workbook.Worksheets("Sheet1").Range("C2") = 123
End Sub

Author

Commented:
Wow! That's magic. I got that bit of code to update column charts on any Slide that I choose!

John, you're such a great teacher I answered my own next questions.
viz.
(1) how can I close the Excel workbook that opens and
(2) how can I stop the Excel workbook from opening at all.

Answer:
    ocht.ChartData.Workbook.Close
    Set oshp = Nothing

and I commented out the
    'ocht.ChartData.Activate

I'm fine for a bit ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial