Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
pasting excel data in a powerpoint slide | Excel Discussion (Misc queries) | |||
can a workbook with macros created in excel 2003 work in excel 20. | Excel Discussion (Misc queries) | |||
Macro in Excel 2002 to save a workbook to a FTP location | Excel Discussion (Misc queries) | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) |