Importing Data From Excel into PPT Charts and Graphs using VBA

[Todd Waldron]

Hi all,

I'm running MS-Office 2003 and I have a PPT template (9 slides) that contain
fixed slide objects (text boxes, charts, graphs) where I need to frequently
update the data in the objects from an Excel data file using VBA.

I'm using MS-Access as the UI and COM controller; I've added the Excel and
PPT library references; through VBA - I've got working instances of the
applications, and successful connections to the Excel data file and the PPT
template.

The snag I've run into is I'm not sure how to refer to the chart/graph
objects to make updates. I've determined that the chart and graph object
types in my template are "Microsoft Graph Chart". What I want to do is
select a range from the Excel data file, copy, then paste the range into the
datasheet behind the respective chart/graph.

I recorded a macro for a graph in PPT where I manually accessed the
datasheet behind a graph object and typed in some data. I wanted to see if
it would show me how to refer to the graph object. Below is what the macro
wrote:

ActiveWindow.Selection.SlideRange.Shapes("Object 3").Select
ActiveWindow.Selection.ShapeRange.OLEFormat.DoVerb Index:=1
ActiveWindow.Selection.Unselect

Is there a way to determine the ID, Name, or Idex of the chart/graph objects
so I can call and update them? Is there an object library I can refer to for
"MS Graph Chart"?

Since I'm using MS-Access as the COM controller I found and added the
Microsoft Graph 11.0 Object Library (C:\Program Files\Microsoft
Office\Office11\graph.exe) just in case.

Any help would be greatly appreciated!

Thank you,

Todd Waldron
Austin, Texas

 

[Brian Reilly, MVP]

It's fun for us to track multiple messages so we will ignore your
repost (g)
Now, the real issue is getting data from Access into PPT whether it
goes though Excel or not? That is a Yes or No answer (been talking to
too many lawyers lately).

Macro Recorder is a nice thing, we all use it but it is frankly weak.
That said, let me ask a few questions and offer an alternative.

Q.1 Why are you using MS Graph in the first place?
Q. 2 Can't you make the chart in an Excel Chart and copy and paste
just the "DataSheet and Chartsheet" into PPT. I would make life
easier.

If it were me doing this, and I do a lot of this, literally hundreds
of thousands of PPT pages per year based on Access data.

My personal prefernces are to NEVER use MS Grump (Graph) for a variety
of reasons.
1. Graphics issues are easier to handle in Excel
2. Calculations on cells work in XL and not in Grump
3. Somewhat more difficult to prgram and get programming
support. XL support is HUGE.

So enough of my over reaction to using MS Grump.

Now onto, my favorite topic of the day, spent a fair amount of time on
the phone today with my fellow MVP friend, Steve Rindsberg would be
the name, talking about Tags in PPT

If you know tags, (if you don't look in PPT Help for more info.)
Sounds like you are programming smart and will get the concept fairly
quickly. Simply said, Tags would let you automatically update the data
from Excel and/or Access with a single button click, or you could
write a little AutoStart routine into a PPT addin that would do this.

Now, other MVP's might say to copy and paste Link as an OLE object. I
don't say that approach is wrong, it is just fragile and not "bullet
proof" as deveopers like to refer to coding.

Feel free to add back to this thread. I'm sure you will hear from
other MVP's who agree or disagree with me. That's the fun of the
Newsgroup. Open and honest. Heck, we all learn here by sharing.
Echo (MVP) taught me a new thing yesterday and I am not ashamed to
admit that I don't know everything. Heck, I've taught Echo a trick or
two in the past. Maybe it was on Halloween when we can do Trick AND
Treat.

Brian Reilly.MVP

 

[Todd Waldron]

Hi Brian,

Thank you for your response. I can re-create the charts/graphs in Excel. I
inherited this PPT template, whoever created it originally chose MS Graph for
the charts/graphs. I'll also check out the tags you mentioned. Thanks
again, I'm sure I'll be back with more questions!

Todd Waldron
Austin, Texas

 

[Todd Waldron]

Hi Steve,

Thank you for your response, your sites have a lot of great stuff! For this
particular problem I think I'll re-create the charts/graphs in Excel so they
will be easier to work with. Thanks again.

Todd Waldron
Austin, Texas

 

[Todd Waldron]

Hi Brian,

Thank you again for your response. I'm open to using tags to update my
charts and graphs, however I'm really short on time to complete this project
and feel uneasy about trying to dive into a new concept this late in the
game. Is there a way to continue by using the following approach (so I can
keep things consistent)?

For example, below is a section of code that I'm using to update a text box
on one of my slides:

***Code Start***

'Copy the range from the Excel data file for Slide 6
wksWorkSheet.Range("G1:G9").Copy

'Paste the range from the Excel Data file to Slide 6
appPPT.ActiveWindow.View.GotoSlide (6)

With appPPT.ActiveWindow.Selection
.SlideRange.Shapes("Rectangle 3").Select
.ShapeRange.TextFrame.TextRange.Select
.ShapeRange.TextFrame.TextRange.Characters(Start:=1,
Length:=24).Select
appPPT.ActiveWindow.View.PasteSpecial (ppPasteText)
.ShapeRange.TextFrame.TextRange.Characters(Start:=1,
Length:=17).Select
.TextRange.Font.Size = 16
.ShapeRange.TextFrame.TextRange.Paragraphs(Start:=1,
Length:=9).ParagraphFormat.Alignment = ppAlignRight
.Unselect
End With

***Code End***

My approach so far has been to copy the new data from my Excel data file,
then in the PPT, navigate to the corresponding slide, navigate to the
corresponding slide object, access the slide object, and paste the new data.

The fixed slide object types in my presentation template include:
Text Boxes
Excel DataSheets (displayed)
Excel Graphs (with underlying Excel DataSheet)
Excel Charts (with underlying Excel DataSheet)

My only hurdle at this point is learning how to update the Excel objects.

For example, on one slide I have two different objects, an Excel DataSheet
(displayed) and an Excel Graph (with underlying Excel DataSheet), both of
which need to be updated. In this case how do I access the Excel objects,
and how do I differentiate between the displayed Excel datasheet and the
underlying datasheet for the graph? Would you be so kind as to provide an
example of how you would approach this using the method I'm describing, or an
example using the tags as you've described? I would be most grateful.

Thank you,

Todd Waldron
Austin, Texas

 

[avi]

Dear Todd ,

Following your interst in Excel to PowerPoint export, I am glad to
announce the release of EzPaste-xl2ppt, a software aimed at
completely automating the task while providing maximum flexibility.
The product could improve drastically productivity

For more details please consult http://www.EzPaste.net

Comments and suggestions will be welcomed

Thanks for your attention

Avi
Metrics institute

 

[Francisco.Duque]

I am trying to link an excel file and a powerpoint file, so that if i
change some cells in excel (let's say a number within a cell ) , the
powerpoint will update with that number (eg.: "the weather on sunday
was [0C]"). Note that the text (weather on sunday was) is typed in
powerpoint and the only link item would be "0C".

I have noticed that this can be possible within microsoft word (paste
special -> Paste Link -> Unformatted text) but this option is not
available within Powerpoint.

does anyone have a solution?

many thank in advance

 

[Brian Reilly, MVP]

In addition to Steve's question, what version of Office are you using?
Behavior changes over versions but the underlying ability to link is
in there since at least Office 97.

Brian Reilly, MVP

 

[Brian Reilly, MVP]

Steve,
If I understand you correctly, that is not what I get here in 2003.
Paste Special always gives me an XL object as a choice. Paste gives me
a PPT Table. Check it with Uber Piggie. Or did I misunderstand you
again?

Brian Reilly, MVP

 

[Brian Reilly, MVP]

Steve,
I have never seen that happen. I always have Excel Object as top of
the list for Paste Special.

Brian Reilly, MVP