+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Hari Prasadh
    Guest

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

    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




  2. #2
    Jon Peltier
    Guest

    Re: Automate Excel to powerpoint - Graphs along with Datasheet (notworkbook)

    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...b;en-us;267974

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

    Hari Prasadh wrote:

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


  3. #3
    Hari Prasadh
    Guest

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

    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.

    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.

    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

    "Jon Peltier" <[email protected]> wrote in message
    news:[email protected]...
    > 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...b;en-us;267974
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >




  4. #4
    Jon Peltier
    Guest

    Re: Automate Excel to powerpoint - Graphs along with Datasheet (notworkbook)

    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


    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


    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.


    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.


    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/
    _______

  5. #5
    Hari Prasadh
    Guest

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

    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.

    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

    "Jon Peltier" <[email protected]> wrote in message
    news:[email protected]...
    > 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

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

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

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

    >
    > 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/
    > _______




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1