Automate Excel to powerpoint - Graphs along with Datasheet (not workbook)

  • Thread starter Thread starter Hari Prasadh
  • Start date Start date
H

Hari Prasadh

Hi,

Somebody in my company had automated powerpoint report containing some text
and charts/graph. The person has left the company and macros arent available
but I have a sample output with me. I have to recreate the macros now. The
data resides in EXCEL and am interfacing to powerpoint by using Jon P's
code.

There is one particular slide in the template with which Iam having a
problem. It has 5 graphs and all of the them seem to be of the same type
look-wise. When I double-click on the graph instead of an excel worksheet ,
a DATASHEET opens up.

a) What is this datasheet?. Difference between this datasheet and having the
full excel workbook available in Powerpoint? I believe that for this slide
the requirement might have been to have not the full excel workbook but the
data behind the graph only. So, is datasheet facility used for that purpose?

b) Im sure that the data for this datasheet must also have come from Excel.
So, my question is how do I automate the production of this graph/datasheet.

c) Can this kind of a graph from datasheets be made in excel? If yes, then
for accomplishing part b) shud I first make the graph in excel and then
paste it in to Powerpoint. If so how to paste them in powerpoint such that I
get the data behind the graphs also (Dont want the full excel workbook). If
this datasheet based graph is available only in Powerpoint then how would
data from excel be converted to graph in powerpoint?

Please guide me for the same.

Thanks a lot,
Hari
India
 
Hi Hari -

The symptoms you describe indicate an infection with MSGraph, the small
Office Applet used by PowerPoint and Word (and I guess by Access) for
simple charting. These charts were not created in Excel.

You can convert these charts into Excel charts, and treat them as all
the others in the presentation. Or you can try the VBA example in the
following Microsoft Knowledge Base article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;267974

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi Jon,

Thnx a lot for offering information on BOTH -- convert these charts into
Excel charts -- and -- Or you can try the VBA example --.

Based on the whims of my manager I would be able to use both of them
depending on the needs of the situation.
I liked the KB article a lot, can copy the code as it is.
The symptoms you describe indicate an infection with MSGraph, the small
Office Applet used by PowerPoint and Word (and I guess by Access) for
simple charting. These charts were not created in Excel.
Click to expand...
You named it as -- MSGraph -- To check whether/why it not available in Excel
I went to Insert - Object - createnew object --Microsoft graph chart and am
able to get such data sheets in Excel as well.

One doubt.
the small Office Applet used by ...... for simple charting.
Click to expand...
Based on your usage of the word -- simple charting-- I wanted to ask about
the pros and cons of
a) pasting as a picture Vs
b) pasting with the excel workbook data Vs
c) using DATASHEET/GRAPH.

I have read through the notes in your web-site and here are some of my
thoughts based on the same.

Somehow Im not much enthused by option a). Because once I have made the
graphs automatically then another person (co-employee/manager/client/) might
like to check whether the graphs have been made correctly or not. Problem
with a picture is that *automatic/easy comparison* is not possible. It's
like a picture would have value labels but one would have to visually
compare those value labels with the base workbook. On the other hand if
powerpoint contains the data in a workbook (assuming that the OLE workbook
contains only graph data) along with the graph then if one double-clicks on
it comparatively easier/automatic comparison could be done between this OLE
workbook and base workbook by using IF formulas etc..

Also, if data changes slightly on a later data for the same powerpoint
presentation then the client or co-employee could just change that single
data point and still get the job done. Not only that but suppose on a later
data with the same data they want a different CHART TYPE (it's like people
might want to *experiment* with choosing a chart which best complement the
*data* available on the hand) then picture wouldnt work.
(Not a personal query but... In the course of your charting profession dont
your clients *prefer* or *demand* the ability to edit charts? Whats the best
practice or the prevailing trend in such scenarios. )

With option b) I agree that it bloats the size and sensitive data might be
unintentionally shared. To combat this you have suggested to paste just the
data and chart in to a new empty workbook, which is a nice idea.

Im new to Datasheets. Why are you referring to option c) being for simple
charting only. When I right click on them Iam able to get the same set of
chart types standard and custom as in a normal Excel chart. It even has the
secondary axis chart option. So by simple charts do you mean that it cannot
make fancy charts such as in Andy pope's or Tushar's site. Actually for this
particular slide I need only normal charts. Or is there some LIMITATION
between datasheet-graph and normal excel graph even when we have to make
normal graphs.

In all what DEMANDS/NEEDS make one choose between b) or c) while making
editable graphs? In my case, the sample presentation was made using a
mixture of datasheets kind of graphs and workbook kind of graphs. All the
graphs are of simple type. Nobody in my company knows why such a combination
was made. I think client might not have asked for this mixture. So am at my
wits end as to why within the same presentation, ONE slide has
datasheet-graphs while the OTHER slide has excel workbook graph. Are there
ANY FACTORS OTHER than size/sensitivity due to which one would choose b) in
one situation while c) in another situation? (Both size/sensitivity of
sharing data has already been dealt with by pasting data in to empty
excelworkbook.)
(And yes am sure that both the slides have been generated automatically
since there are 500 such presentations..)

Thanks a lot,
Hari
India
 
Hari -
Based on your usage of the word -- simple charting-- I wanted to ask about
the pros and cons of
a) pasting as a picture
Click to expand...

When I worked as an engineer, I used this approach for two reasons. (1)
it was a convenient visual way to archive status at a given point, as
the charts were static. (2) I could submit reports without worrying that
my supervisor and his supervisor would not be able to distort my
message. They were not interested in seeing how another chart type might
look (they wouldn't know a line chart from the Magna Carta). They were
only concerned about making things look "better" or at least "not so
bad", and numerical engineering was not out of the question.

Most of my clients are pretty comfortable within Excel, and only want
exports to Word or PowerPoint for reporting purposes. They prefer to
rework the data in Excel rather than in the report. Pasting pictures is
a lightweight way to do this. If they need to make changes, it's
generally because the bulk of the report is changing (additional data
has come in, or it's time for next month's report). It makes more sense
for the VBA to generate a new report from Excel, than to have linked
charts interspersed within text that must be redone.
b) pasting with the excel workbook data
Click to expand...

Alternatives:

1. Copy the chart sheet into an otherwise empty workbook, and copy this
chart into PowerPoint. The data will not be available upon editing or
opening the chart.
2. Copy the chart in Excel, and do Paste Link in PowerPoint, but I
believe this is only possible in VBA starting in 2003.

This allows the chart to have any of the advanced custom features
allowed in Excel.
c) using DATASHEET/GRAPH.
Click to expand...

This can be done within Excel, as you've discovered, but it insulates
the MSGraph chart from the Excel data.

I would only keep this option if it were important to maintain
conformance with existing charts, and if the charts were simple ones.
However, the nature of MSGraph is that you lose most of your control
over independent series in the chart. Your choice of combination charts
is limited. You can't link text elements to cells in the datasheet. You
can only have one set of X values in the entire chart, in the row above
row 1 or the column left of column A. In general I have also had
difficulties programming MSGraph which are not present using Excel.
Nobody in my company knows why such a combination was made.
Click to expand...

I suspect that this was not a conscious design decision, and it may even
have been made by different people or by one person at different times.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi Jon,

Thnx a lot for your detailed reply.
I suspect that this was not a conscious design decision, and it may even
have been made by different people or by one person at different times.
Click to expand...
This is a clincher argument. I gather now that design decision was made by 2
different people from the client side.
(Im amazed by your assertion -- or by one person at different times.-- I
could never have thought of angle.)

Thanks a lot,
Hari
India
 
Back
Top