PowerPoint Automation

[Guest]

Hello All,

Using: Office 2003 Professional

Please excuse me if my post is to general, but I do not have much experience
with PP. I am well versed with Excel and Access and familliar with VBA. At
my workplace, we generate a very large PP Presentation. Slide 1 has a chart,
a spredsheet, a photo and a couple of text boxes. Slide 2 has a laerg
textbox and Slides 3-? (depending on how much data there is to display) is a
spreadsheet that may continue on for several slides. Then the next slide
starts it all over again with the same format but different data. Th total
amount of slides can be as high as 60+. As of now they are all updated by a
fleet of personnel manually.
1. I would like to know how I can automatically generate this PP Pres with
the use of Access or Excel as a data source?
2. Can slide 2 be set up to mailmerge fields?
3. Can slide 3-? be one spreadsheet and made to automatically overflow to
the next slide with field/column headers on the new slide?
4. Can the photo in in slide 1 be fed from an OLE field in access or auto
filled some other way?
5. What is the easiest way to automate the fill for my Chart as all of the
data for this project can probably be sourced from 1 table, but would require
either a filter or a query for each part. (they are currently embedding or
linking the same file over and over with a different view of the data).

Sorry if my questions are convoluted.

 

[Steve Rindsberg]

Please excuse me if my post is to general, but I do not have much experience

with PP. I am well versed with Excel and Access and familliar with VBA. At
my workplace, we generate a very large PP Presentation. Slide 1 has a chart,
a spredsheet, a photo and a couple of text boxes. Slide 2 has a laerg
textbox and Slides 3-? (depending on how much data there is to display) is a
spreadsheet that may continue on for several slides. Then the next slide
starts it all over again with the same format but different data. Th total
amount of slides can be as high as 60+. As of now they are all updated by a
fleet of personnel manually.
1. I would like to know how I can automatically generate this PP Pres with
the use of Access or Excel as a data source?
2. Can slide 2 be set up to mailmerge fields?
3. Can slide 3-? be one spreadsheet and made to automatically overflow to
the next slide with field/column headers on the new slide?
4. Can the photo in in slide 1 be fed from an OLE field in access or auto
filled some other way?
5. What is the easiest way to automate the fill for my Chart as all of the
data for this project can probably be sourced from 1 table, but would require
either a filter or a query for each part. (they are currently embedding or
linking the same file over and over with a different view of the data).

Sorry if my questions are convoluted.

It's a complicated project. I thought your questions were quite clear.

There are a few inexpensive off-the-shelf solutions that can do at least some of
what you're after. Our PPT Merge (http://merge.pptools.com) will automate item
2 and 4, with the data taken from Excel or CSV/Tab-Del text files. Kurt will
post a link to his product's site shortly, I'm sure.

3 would be trickier; it might force you to create a link to the spreadsheet data
test to check the height and if it's over the slide height, delete the link,
recreate it, this time to a row or column less data, test again and so on until
you've got a link that fits on one slide. Then add another slide and pick up
where you left off. You'd probably create two links on each slide, one for the
data, one for the headers.

5 would take some thought, but if the data source is the same from one
presentation to the next, you might be able to indicate the data range with tags
on shapes in PPT or the like.

 

[Guest]

Thanks for you response Steve,

I pretty much grasped everthing you related but the reference to Item 5: I
am not familliar with Tags or shapes although I can pretty much guess what
the are in general. could you fill me in on how they apply to this and how
to use them? Also is there a way, once I get the basic mechanics of this
thing locked down, to automaically generate this presentation with the
repeating cycle of of slides (Type 1, 2, 3-? from my first post being a
cycle, each cycle variing in length)?

Thanks again,

Patrick

 

[Guest]

Hello,
Please don't take the following personally, I know bosses ask for some crazy
things - but do you really want to do what you desribed?

There must be a better
communications strategy for the above data than automatically putting it
into a PPT presentation and then forcing a group of people to sit down and
watch 60 slides of this. (correct me please if I am presuming too much).
Just because you might be able to do this technically, doesn't mean that
you want
to do it. This scenario might result in a classic case of "PowerPoint
Poisoning."

Again, I don't mean this personally in any way - but I think the above
scenario is
a real abuse of the technology that is not going to accomplish any real goal
that I can imagine.

I thik I can speak with at least a small bit of authority on this subject.
I teach
finance in a European MBA program, have a Ph.D. and CFA designation. My
wife is
a professional accountant who always tells me that the real challenge in her
job is not cranking out the numbers but communicating them.

So, in short, your individual request is none of my business - but as a
general
principle this type of thing looks amazingly bad.

Sincerely,
Pete

 

[Guest]

I agree vocally and whole heartedly with your comment Pdek. I am a
govornment contractor and it seems the particular customers that I work for
belive that everything... and I mean everything belongs on a PPT
presentation... even if it will not be presented. I am however obliged to
help them accomplish this... What am I to do. LOL

Tahnks for the comments,

Patrick

 

[Guest]

Ah! A request from the government - that explains it all!
Good luck and all the best,
Pete

 

[Kurt]

Also have a look at www.take-off.as/datapoint for connecting your
presentation to excel and databases of all kinds.
Kurt

 

[Steve Rindsberg]

Now that we both agree that we both agree with Pete and that we all three agree that
heavy sigh shrug of shoulders there's nothing to be done about it ... ;-)

I pretty much grasped everthing you related but the reference to Item 5: I
am not familliar with Tags or shapes although I can pretty much guess what
the are in general.

Shapes: nearly everything on a PPT slide is a shape; it might be a rectangle, text
box, OLE embedded object from Excel ... whatever.

Tags: nearly everything in PPT can have a collection of tags (named strings, more or
less like per-object variables). These can be very handy for storing odd bits of
info. For example, on the "template" for your slide that gets the Excel data, you
might add a rectangle that indicates where the Excel data is to go and a maximum size
for it. The rectangle might have tags like:
Tag name Value
ExcelFile c:\whatever\somefile.xls
Range A122
RangeName MyData

and so on. VBA can read the tag values to work out what file to open and where to
look within it to get the data for this particular slide.

Also is there a way, once I get the basic mechanics of this
thing locked down, to automaically generate this presentation with the
repeating cycle of of slides (Type 1, 2, 3-? from my first post being a
cycle, each cycle variing in length)?

Almost certainly; but while we know what controls how many slides there are in 3-??,
we don't know what controls the number of cycles. Fill in that bit and we can go
from there.

 

[Guest]

Steve,
Sorry for not picking up this thread again sooner. We are in the midst of
moving to Active Directory and my priorities had to shift for a while. The
thing that determines how many cycles are going to run is the amount of data
groups. We have a number of different fleets of equipment that we track the
maintenance status on. If a fleet , for a reporting period, (16th of 1 month
to the 15th of the next month) is projected to fail with regards to
maintenance, that fleet will show up in the presentation as a cycle of
slides. So, the data will continue to generate slides until the data source
runs out of failing fleets.

Thanks,
Patrick

 

[Steve Rindsberg]

Patrick,

I have a lousy imagination for this sort of thing; can you cobble up some
simplified (bogus) data that illustrates what you're dealing with and explain what
you want that to turn into?

 

[Guest]

I have made up a mock-up set of slides (1 Cycle) with Fictitios info. Can I
send them to you or post them somehow?

Thanks

 

[Steve Rindsberg]

I have made up a mock-up set of slides (1 Cycle) with Fictitios info. Can I
send them to you or post them somehow?

Sure. ZIP them up with an example of the data the slides came from and post them on a
web or ftp site; drop the URL back here so we can have a look.

 

[Guest]

Steve,
Here is a link to the Mock Presentation. Currently the data is manually
input but I will use an excel workbook or a query from access ((especially if
I can code the SQL statement directly into PowerPoint) whichever is easier.

http://home.ripway.com/2003-2/267/Presentation/Presentation1.ppt

Thanks,

patrick

 

[Steve Rindsberg]

Patrick,

Thanks, that helps. So this presentation represents one cycle of slides (ie,
there wouldn't be any others for this particular fleet, there'd be one set like
this for each fleet projected to fail in some way)?

Will the layouts stay consistent no matter what (just the data changes)?

Will the data be fairly consistent (that is, one paragraph of XX words worth of
discussion, for example; possibly no discussion but never more than 2 * XX words
of discussion)?

If so, you might consider coding the whole thing in Excel or Access (since the
heavy lifting is in selecting the data, no?) You could automate PPT to create new
slides and feed data into "merge fields" on the slides themselves (look for a bit
of text like !FleetName! and replace it with the fleet name from the current data)

You might want to get in touch with Brian Reilly (one of the PPT MVPs). He does a
lot of this kind of thing. www.reillyand.com

As long as you don't plan to let him do any graphic design for you, there's no
need to be upset by the design of his site. It's almost as ugly as mine!

Steve,
Here is a link to the Mock Presentation. Currently the data is manually
input but I will use an excel workbook or a query from access ((especially if
I can code the SQL statement directly into PowerPoint) whichever is easier.

http://home.ripway.com/2003-2/267/Presentation/Presentation1.ppt

Thanks,

patrick

 

[Guest]

Thanks, that helps. So this presentation represents one cycle of slides
(ie,

there wouldn't be any others for this particular fleet, there'd be one set like
this for each fleet projected to fail in some way)?
Yes.

Will the layouts stay consistent no matter what (just the data changes)?

Yes, for the most part, there will however be some freetext on the Narative
(Info Paper). Hoping that part could be done in Word and joined to the
Automation.

Will the data be fairly consistent (that is, one paragraph of XX words worth of
discussion, for example; possibly no discussion but never more than 2 * XX words of discussion)?

No, The text will need to be flexible.

You might want to get in touch with Brian Reilly (one of the PPT MVPs). He >does a lot of this kind of thing. www.reillyand.com

I will check his site out. Do you have any tips on how to get started with
the tags? I do not find much comprehensive info on the web however my search
terms may not be very effecteive (May not kow enough about them to ask the
right questions). I tried 5to use the sample code on the MS website and
could not make it work. I sure hope you can get me moving in the right
direction.

Thanks,
Patrick

 

[Steve Rindsberg]

I will check his site out. Do you have any tips on how to get started with

the tags?

Do you mean the "merge fields" I mentioned earlier? (Probably best not to use
the term "tags" as it has a specific meaning in PPT VBA). (Unless you mean that
specific meaning.) (Of course.)

Have a look at http://merge.pptools.com to get an idea how at least parts of this
might be handled. That add-in looks at the first row of a data file for things
like this:

Name :email: !whatever!
Steve not@all something else

It then scoots through all the text in a presentation looking for the fields
"defined" in the first row and replacing them with the data in successive rows.
The field names can be anything, but it's a good idea to make them unique.

"Name" above is a good example of a bad merge field name - consider what happens
when it finds this:

My name is name
Go to the booth named "Information"

My Steve is Steve
Go to the booth Steved "Information"

Not helpful.

My name is :Name: -- that works better.

 

[Guest]

Do you mean the "merge fields" I mentioned earlier? (Probably best not to
use

the term "tags" as it has a specific meaning in PPT VBA). (Unless you mean that
specific meaning.) (Of course.)

Actually I meant the "Tag Object" in vba. I did contact Brian and he called
me. He advised that most of my coding should be in Excel with the PPT
reference turned on. This at least gets me on the right starting block. I
understand the basics of the Tag Object as you described it but as I am not a
VBA Guru like you and Brian I am probabbly going to stumble over my syntax
and finding the right objects for a while. I suspect since this thread is
awfully long already as it is and I believe you have now got me on the right
track, I will consider this question answered. I will however look forward
to your and the other member's assistance in future threads on this project
as it progresses and I run into snags.

Thank you so much for the help on this as well as for putting me in contact
with Brian.

Big Kudos,
Patrick

 

[Steve Rindsberg]

Actually I meant the "Tag Object" in vba. I did contact Brian and he called

me. He advised that most of my coding should be in Excel with the PPT
reference turned on. This at least gets me on the right starting block. I
understand the basics of the Tag Object as you described it but as I am not a
VBA Guru like you and Brian I am probabbly going to stumble over my syntax
and finding the right objects for a while.

Trust me, I know a few VBA Gurus (Gurim? Gurii?) and Brian's no VBA Guru, though he
looks like a Wizard when he stands next to me. We both stumble our way through
stuff too.

In case it helps, each shape, slide or presentation can contain a collection of
tags; tags are sort of like named variables that hold string data.

So for example to store some odds and ends of info at the presentation level:

ActivePresentation.Tags.Add "Author", "Brian's Cat"

That creates an Author tag that contains the value Brian's Cat

So later if you want to check on the pawprints of a presentation:

If ActivePresentation.Tags("Author") = "Brian's Cat" Then
' We're happy
MsgBox "Purrrrrrrrr"
Else
' Chase them away
MsgBox "Arf! Arf! Grrrrrrrr!"
End If


I suspect since this thread is

awfully long already as it is and I believe you have now got me on the right
track, I will consider this question answered. I will however look forward
to your and the other member's assistance in future threads on this project
as it progresses and I run into snags.

You know where to find us!