Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build custom charts in Excel and copy them to PowerPoint
Apologies in advance for the lengthy request. I have no idea if this is even
possible to do with a macro. I have roughly 350 lines of data that I need to create charts from. Each line will generate its own chart. I need cylinder charts that fit the following criteria (all chart options set to default unless noted differently below): 960 pixels (136.43 column width) wide X 578 pixels (34 cells @ 12.75 row height) high The Chart Title is in the A column The B1 cell contains the x-axis label "Min" The C1 cell contains the x-axis label "Median" The D1 cell contains the x-axis label "Data" The E1 cell contains the x-axis label "Max" The "Min" Cylinder should be yellow The "Median" Cylinder should be blue The "Data" cylinder should be red The "Max" cylinder should be black Data labels should be present, above the appropriate cylinder, and should show value The Chart Area should have no border and no background Gridlines should be Gray - 25% The Floor Area should also be Gary - 25% After all of this, the chart should be copied to its own slide in an open PowerPoint Presentation, the chart should delete (because of Excel's built-in number-of-chart limitations), and the process repeat until all 350 or so rows have been charted. Simple, right? Since I know next to nothing about vb, macros, and the like, it would be much appreciated if any responses could include which references to turn on, etc. Also, since I'm guessing that a macro like this will take some time to write, would anyone who decides to tackle this please let me know you are doing so? I don't mind waiting in the least, but if this is too daunting of a challenge, I'd like to know that no one is working on it so I can get to work on building these charts manually. Many thanks. :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build custom charts in Excel and copy them to PowerPoint
I would set up dynamic ranges (names). Cell F1 contains a number between 1
and 350 (or whatever number of lines you need to plot). Create names by going to Insert menu Names Define. The names and their refers-to formulas are he Name Refers To MyTitle =OFFSET($A$1,$F$1,0) MyMin =OFFSET($B$1,$F$1,0) MyMed =OFFSET($C$1,$F$1,0) MyDat =OFFSET($D$1,$F$1,0) MyMax =OFFSET($E$1,$F$1,0) So when I change the value in F1, a different row is referenced in these names. The chart will use these names, so changing F1 changes the chart. Make the chart using any row of data. Change the series formula as follows. Series 1 will look like this assuming row 2 was used to create it and the worksheet is named Sheet1: =SERIES(Sheet1!$B$1,,Sheet1!$B$2,1) Change it to this: =SERIES(Sheet1!$B$1,,Sheet1!MyMin,1) Excel is likely to change it to =SERIES(Sheet1!$B$1,,Book1.xls!MyMin,1) change the other cell references to the names created above. Now you need some kind of macro. I have some sample code to copy Excel charts to PowerPoint he http://peltiertech.com/Excel/XL_PPT.html In the macro, set up the link to PowerPoint first, then set up a loop that works like this (pseudocode): For i=1 to 350 ActiveSheet.Range(:F1:).Value = i ' create a new slide (use code from the web site) ' copy the chart ' paste onto the new slide Next - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "mustang25" wrote in message ... Apologies in advance for the lengthy request. I have no idea if this is even possible to do with a macro. I have roughly 350 lines of data that I need to create charts from. Each line will generate its own chart. I need cylinder charts that fit the following criteria (all chart options set to default unless noted differently below): 960 pixels (136.43 column width) wide X 578 pixels (34 cells @ 12.75 row height) high The Chart Title is in the A column The B1 cell contains the x-axis label "Min" The C1 cell contains the x-axis label "Median" The D1 cell contains the x-axis label "Data" The E1 cell contains the x-axis label "Max" The "Min" Cylinder should be yellow The "Median" Cylinder should be blue The "Data" cylinder should be red The "Max" cylinder should be black Data labels should be present, above the appropriate cylinder, and should show value The Chart Area should have no border and no background Gridlines should be Gray - 25% The Floor Area should also be Gary - 25% After all of this, the chart should be copied to its own slide in an open PowerPoint Presentation, the chart should delete (because of Excel's built-in number-of-chart limitations), and the process repeat until all 350 or so rows have been charted. Simple, right? Since I know next to nothing about vb, macros, and the like, it would be much appreciated if any responses could include which references to turn on, etc. Also, since I'm guessing that a macro like this will take some time to write, would anyone who decides to tackle this please let me know you are doing so? I don't mind waiting in the least, but if this is too daunting of a challenge, I'd like to know that no one is working on it so I can get to work on building these charts manually. Many thanks. :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build custom charts in Excel and copy them to PowerPoint
Jon,
Thanks much for the reply. I fear that my inexperience is going to lead me to ask some rather simple (to you) questions. 1 - You asked me to use the Insert Names Define function. Should I Define the entire column all at once with the OFFSET formulas you listed, just the top cell in each column, or each cell individually? If individually, is there a unique name I should give each cell? 2 - When I redefine the series (which I'm doing after creating and customizing the charts by using the Source Data option and replacing the "Value" field) from "=Sheet1!$B$2:$E$2" to "=SERIES(Sheet1!$B$1,,Sheet1!MyMin,1)" (minus the quotes, of course) I get a "Formula contains an error" message. I must be missing something here. 3 - I'm not real sure what you meant by "change the other cell references to the names created above." Could you clarify? As a side note, I've used your Excel to PowerPoint macros before with AMAZING results. I am concerned though because the macro I used only works on existing charts and I know my computer does not have enough memory to allow Excel to create 350 or so charts in one workbook (hence my original post mentioning that the chart could be deleted from the workbook after it is copied to PowerPoint). Any suggestions in this area? Thank you again, you've personally helped me a lot in the past and I appreciate your efforts now. "Jon Peltier" wrote: I would set up dynamic ranges (names). Cell F1 contains a number between 1 and 350 (or whatever number of lines you need to plot). Create names by going to Insert menu Names Define. The names and their refers-to formulas are he Name Refers To MyTitle =OFFSET($A$1,$F$1,0) MyMin =OFFSET($B$1,$F$1,0) MyMed =OFFSET($C$1,$F$1,0) MyDat =OFFSET($D$1,$F$1,0) MyMax =OFFSET($E$1,$F$1,0) So when I change the value in F1, a different row is referenced in these names. The chart will use these names, so changing F1 changes the chart. Make the chart using any row of data. Change the series formula as follows. Series 1 will look like this assuming row 2 was used to create it and the worksheet is named Sheet1: =SERIES(Sheet1!$B$1,,Sheet1!$B$2,1) Change it to this: =SERIES(Sheet1!$B$1,,Sheet1!MyMin,1) Excel is likely to change it to =SERIES(Sheet1!$B$1,,Book1.xls!MyMin,1) change the other cell references to the names created above. Now you need some kind of macro. I have some sample code to copy Excel charts to PowerPoint he http://peltiertech.com/Excel/XL_PPT.html In the macro, set up the link to PowerPoint first, then set up a loop that works like this (pseudocode): For i=1 to 350 ActiveSheet.Range(:F1:).Value = i ' create a new slide (use code from the web site) ' copy the chart ' paste onto the new slide Next - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "mustang25" wrote in message ... Apologies in advance for the lengthy request. I have no idea if this is even possible to do with a macro. I have roughly 350 lines of data that I need to create charts from. Each line will generate its own chart. I need cylinder charts that fit the following criteria (all chart options set to default unless noted differently below): 960 pixels (136.43 column width) wide X 578 pixels (34 cells @ 12.75 row height) high The Chart Title is in the A column The B1 cell contains the x-axis label "Min" The C1 cell contains the x-axis label "Median" The D1 cell contains the x-axis label "Data" The E1 cell contains the x-axis label "Max" The "Min" Cylinder should be yellow The "Median" Cylinder should be blue The "Data" cylinder should be red The "Max" cylinder should be black Data labels should be present, above the appropriate cylinder, and should show value The Chart Area should have no border and no background Gridlines should be Gray - 25% The Floor Area should also be Gary - 25% After all of this, the chart should be copied to its own slide in an open PowerPoint Presentation, the chart should delete (because of Excel's built-in number-of-chart limitations), and the process repeat until all 350 or so rows have been charted. Simple, right? Since I know next to nothing about vb, macros, and the like, it would be much appreciated if any responses could include which references to turn on, etc. Also, since I'm guessing that a macro like this will take some time to write, would anyone who decides to tackle this please let me know you are doing so? I don't mind waiting in the least, but if this is too daunting of a challenge, I'd like to know that no one is working on it so I can get to work on building these charts manually. Many thanks. :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build custom charts in Excel and copy them to PowerPoint
1 - You asked me to use the Insert Names Define function. Should I Define the entire column all at once with the OFFSET formulas you listed, just the top cell in each column, or each cell individually? If individually, is there a unique name I should give each cell? Reading ahead, I see I can simplify. I hope I didn't overcomplicate matters. You only need the following names: Name Refers To MyTitle =OFFSET($A$1,$F$1,0) MyData =OFFSET($B$1:$E$1,$F$1,0) Each name is its own definition, and it refers to whichever cell is offest from the top row by the number in cell F1. All the cells don't need names. Just do Insert Names Define, enter the first name (MyTitle) into the Name entrybox and the first refers ro formula (=OFFSET($A$1,$F$1,0)) into the Refers To entrybox. Click Add, then repeat for each of the others in the list. I meant to say, when you create your chart, add a title, then select the title (but don't edit it, there should be no cursor in it), click in the formula bar, and type =Sheet1!MyTitle If your sheet name has a hyphen or space, enclose the name in single quotes: ='Sheet One'!MyTitle This links the chart title to the appropriate cell in column A. 2 - When I redefine the series (which I'm doing after creating and customizing the charts by using the Source Data option and replacing the "Value" field) from "=Sheet1!$B$2:$E$2" to "=SERIES(Sheet1!$B$1,,Sheet1!MyMin,1)" (minus the quotes, of course) I get a "Formula contains an error" message. I must be missing something here. I explained how to edit the formula bar, above the worksheet column headers. Change this formula: =SERIES(Sheet1!$A$1,Sheet1!$B$1:$E$1,Sheet1!$B$2:$ E$2,1) to this: =SERIES(Sheet1!MyTitle,Sheet1!$B$1:$E$1,Sheet1!MyD ata,1) 3 - I'm not real sure what you meant by "change the other cell references to the names created above." Could you clarify? I thought it was more complicated than it was. Sorry. As a side note, I've used your Excel to PowerPoint macros before with AMAZING results. I am concerned though because the macro I used only works on existing charts and I know my computer does not have enough memory to allow Excel to create 350 or so charts in one workbook (hence my original post mentioning that the chart could be deleted from the workbook after it is copied to PowerPoint). Any suggestions in this area? You are not making 350 charts. You make the one Excel chart manually, it is updated automatically when the macro changes the value in cell F1. The same existing chart gets copied as a picture 350 times (after each time the value in F1 changes) and pasted into PowerPoint 350 times on a new slide. So you do need 350 slides, but copying pictures of the chart will not cause a huge resource bottleneck. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build custom charts in Excel and copy them to PowerPoint
Jon,
Thanks so much for the clarification. It helped loads! The scary thing is that I'm starting to understand some of this! I believe that I've done all the things you outlined properly, but am having trouble with the actual macro. I don't know the first thing about vb code, so I'm hoping I can impose on you just one more time. Here is the code I am running: Sub BuildCharts() For i = 1 To 350 ActiveSheet.Range(:F1:).Value = i Sub ChartsToPresentation() ' Set a VBE reference to Microsoft PowerPoint Object Library Dim PPApp As PowerPoint.Application Dim PPPres As PowerPoint.Presentation Dim PPSlide As PowerPoint.Slide Dim PresentationFileName As Variant Dim SlideCount As Long Dim iCht As Integer ' Reference existing instance of PowerPoint Set PPApp = GetObject(, "Powerpoint.Application") ' Reference active presentation Set PPPres = PPApp.ActivePresentation PPApp.ActiveWindow.ViewType = ppViewSlide For iCht = 1 To ActiveSheet.ChartObjects.Count ' copy chart as a picture ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _ Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture ' Add a new slide and paste in the chart SlideCount = PPPres.Slides.Count Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank) PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex With PPSlide ' paste and select the chart picture .Shapes.Paste.Select ' align the chart PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True End With Next ' Clean up Set PPSlide = Nothing Set PPPres = Nothing Set PPApp = Nothing End Sub Next End Sub Obviously, I just copied and pasted the code in one of your previous replies and did the same for one of the macros you wrote on your site. I've evidently done something wrong. When I run this, I get an error for invalid syntax on the third line, "ActiveSheet.Range(:F1:).Value = i". Thank you once again. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build custom charts in Excel and copy them to PowerPoint
Jon,
Never mind, I figured it out! Here is the macro: Sub BuildCharts() For i = 1 To 350 ActiveSheet.Range("F1").Value = i Dim PPApp As PowerPoint.Application Dim PPPres As PowerPoint.Presentation Dim PPSlide As PowerPoint.Slide Dim PresentationFileName As Variant Dim SlideCount As Long Dim iCht As Integer ' Reference existing instance of PowerPoint Set PPApp = GetObject(, "Powerpoint.Application") ' Reference active presentation Set PPPres = PPApp.ActivePresentation PPApp.ActiveWindow.ViewType = ppViewSlide For iCht = 1 To ActiveSheet.ChartObjects.Count ' copy chart as a picture ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _ Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture ' Add a new slide and paste in the chart SlideCount = PPPres.Slides.Count Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank) PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex With PPSlide ' paste and select the chart picture .Shapes.Paste.Select ' align the chart PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True End With Next ' Clean up Set PPSlide = Nothing Set PPPres = Nothing Set PPApp = Nothing Next End Sub The chart has to be built somewhere in the same sheet as the data, a PowerPoint presentation must be open, and (as you did mention) the reference to PowerPoint must be checked. Thank you so much, you have literally saved me DAYS of tedious work! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build custom charts in Excel and copy them to PowerPoint
Sorry about the typo! Glad you figured it out.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "mustang25" wrote in message ... Jon, Never mind, I figured it out! Here is the macro: Sub BuildCharts() For i = 1 To 350 ActiveSheet.Range("F1").Value = i Dim PPApp As PowerPoint.Application Dim PPPres As PowerPoint.Presentation Dim PPSlide As PowerPoint.Slide Dim PresentationFileName As Variant Dim SlideCount As Long Dim iCht As Integer ' Reference existing instance of PowerPoint Set PPApp = GetObject(, "Powerpoint.Application") ' Reference active presentation Set PPPres = PPApp.ActivePresentation PPApp.ActiveWindow.ViewType = ppViewSlide For iCht = 1 To ActiveSheet.ChartObjects.Count ' copy chart as a picture ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _ Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture ' Add a new slide and paste in the chart SlideCount = PPPres.Slides.Count Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank) PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex With PPSlide ' paste and select the chart picture .Shapes.Paste.Select ' align the chart PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True End With Next ' Clean up Set PPSlide = Nothing Set PPPres = Nothing Set PPApp = Nothing Next End Sub The chart has to be built somewhere in the same sheet as the data, a PowerPoint presentation must be open, and (as you did mention) the reference to PowerPoint must be checked. Thank you so much, you have literally saved me DAYS of tedious work! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build custom charts in Excel and copy them to PowerPoint
mustang25 wrote on 11/21/2006 15:25 ET :
Apologies in advance for the lengthy request. I have no idea if this is even possible to do with a macro. I have roughly 350 lines of data that I need to create charts from. Each line will generate its own chart. I need cylinder charts that fit the following criteria (all chart options set to default unless noted differently below): 960 pixels (136.43 column width) wide X 578 pixels (34 cells @ 12.75 row height) high The Chart Title is in the A column The B1 cell contains the x-axis label "Min" The C1 cell contains the x-axis label "Median" The D1 cell contains the x-axis label "Data" The E1 cell contains the x-axis label "Max" The "Min" Cylinder should be yellow The "Median" Cylinder should be blue The "Data" cylinder should be red The "Max" cylinder should be black Data labels should be present, above the appropriate cylinder, and should show value The Chart Area should have no border and no background Gridlines should be Gray - 25% The Floor Area should also be Gary - 25% After all of this, the chart should be copied to its own slide in an open PowerPoint Presentation, the chart should delete (because of Excel's built-in number-of-chart limitations), and the process repeat until all 350 or so rows have been charted. Simple, right? Since I know next to nothing about vb, macros, and the like, it would be much appreciated if any responses could include which references to turn on, etc. Also, since I'm guessing that a macro like this will take some time to write, would anyone who decides to tackle this please let me know you are doing so? I don't mind waiting in the least, but if this is too daunting of a challenge, I'd like to know that no one is working on it so I can get to work on building these charts manually. Many thanks. :) Hello, For copy/paste of hundreds of charts or tables from Excel to PPT (or Word) , EzPaste-xl2anywhere is the perfect tool for you: www.EzPaste.net Good luck AvivExpert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste custom Excel chart to Word/PowerPoint | Excel Discussion (Misc queries) | |||
How can I build a custom list in Excel | Excel Discussion (Misc queries) | |||
Copy Charts from Excel worksheet to PowerPoint | Excel Programming | |||
Macro to copy Excel charts to existing Powerpoint file | Excel Programming | |||
Help! How to add many excel charts into PowerPoint at once? | Charts and Charting in Excel |