asked on

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.

SOLUTION

John Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

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?

hindersaliva

ASKER

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.

hindersaliva

ASKER

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.

hindersaliva

ASKER

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

ASKER CERTIFIED SOLUTION

John Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

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 ...