PowerPoint 2010 - programming charts with VBA

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.
hindersalivaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John WilsonCEO PowerPoint AlchemyCommented:
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
Rgonzo1971Commented:
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
hindersalivaAuthor 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?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

hindersalivaAuthor 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.
hindersalivaAuthor 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.
hindersalivaAuthor 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
John WilsonCEO PowerPoint AlchemyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hindersalivaAuthor 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 ...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.