If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Excel Charts to PowerPoint 2
I am following on from an older post
http://groups.google.com/group/micro...af87f70b6657f? I have used Jon Peltier's page http://peltiertech.com/Excel/XL_PPT.html#chartppt to try and create a macro that will open a new PPT, copy all th echartsheets and paste as bitmap (to stop people falsifying charts!) one to a ppt slide. My problems a 1) The macro only seems to work if PPT is already running (despite using Jon's code for creating a New PPT object) 2) The bitmap solution is ok when on screen but I get people asking if their eyes are playing tricks when we look at the printed copy. Yes it is a bit fuzzy! What controls the resolution of a bitmap? Is it the screen resolution? Is there any way to prevent ungrouping of a picture? 3) Can I define the PPT template (.pot) that should be used? 4) What decides the dimensions (points, inches, cm) used for positioning? - Are they set by the Windows regional settings? The code is below (you can see where I have added sections - extra indent) I am so close to my goal but am stuck with these last few points! Can anyone help? Steve 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 ' Create instance of PowerPoint Set PPApp = CreateObject("Powerpoint.Application") ' Create a presentation Set PPPres = PPApp.Presentations.Add ' Some PowerPoint actions work best in normal slide view PPApp.ActiveWindow.ViewType = ppViewSlide ' Add first slide to presentation Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly) ' Reference existing instance of PowerPoint 'Set PPApp = GetObject(, "Powerpoint.Application") ' Reference active presentation 'Set PPPres = PPApp.ActivePresentation ' Some PowerPoint actions work best in normal slide view 'PPApp.ActiveWindow.ViewType = ppViewSlide For iCht = 1 To ActiveWorkbook.Charts.Count ' Copy chartsheet as a picture ActiveWorkbook.Charts(iCht).CopyPicture _ Appearance:=xlScreen, Size:=xlScreen, Format:=xlBitmap '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 ' position the chart With PPApp.ActiveWindow.Selection.ShapeRange .Top = 94 ' points .Left = 58 ' points .Width = 8.2 * 72 .Height = 5.6 * 72 End With End With Next ' Clean up Set PPSlide = Nothing Set PPPres = Nothing Set PPApp = Nothing End Sub |
#2
|
|||
|
|||
Multiple Excel Charts to PowerPoint 2
You neglected to mention which version of Office you're using.
Bitmaps are only good if they are not resized and not antialiased. Their resolution is based on the screen: one screen pixel = one bitmap pixel. Until 2007, the Picture format was better, but now it has been degraded, but it at least doesn't change in quality as it is resized. I don't know why PowerPoint won't start using CreateObject. What feedback does your machine provide? You should be able to define the PowerPoint template to use. If it's anything like Excel, something like this should work: Set PPPres = PPApp.Presentations.Add("TemplatePathAndFile.pot") I'll leave it up to you to look it up in the PowerPoint object model (use the Object Browser in the VB Editor). When you get your image into PowerPoint, the dimensions you use in VBA for positioning are points. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "SteveG" wrote in message ... I am following on from an older post http://groups.google.com/group/micro...af87f70b6657f? I have used Jon Peltier's page http://peltiertech.com/Excel/XL_PPT.html#chartppt to try and create a macro that will open a new PPT, copy all th echartsheets and paste as bitmap (to stop people falsifying charts!) one to a ppt slide. My problems a 1) The macro only seems to work if PPT is already running (despite using Jon's code for creating a New PPT object) 2) The bitmap solution is ok when on screen but I get people asking if their eyes are playing tricks when we look at the printed copy. Yes it is a bit fuzzy! What controls the resolution of a bitmap? Is it the screen resolution? Is there any way to prevent ungrouping of a picture? 3) Can I define the PPT template (.pot) that should be used? 4) What decides the dimensions (points, inches, cm) used for positioning? - Are they set by the Windows regional settings? The code is below (you can see where I have added sections - extra indent) I am so close to my goal but am stuck with these last few points! Can anyone help? Steve 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 ' Create instance of PowerPoint Set PPApp = CreateObject("Powerpoint.Application") ' Create a presentation Set PPPres = PPApp.Presentations.Add ' Some PowerPoint actions work best in normal slide view PPApp.ActiveWindow.ViewType = ppViewSlide ' Add first slide to presentation Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly) ' Reference existing instance of PowerPoint 'Set PPApp = GetObject(, "Powerpoint.Application") ' Reference active presentation 'Set PPPres = PPApp.ActivePresentation ' Some PowerPoint actions work best in normal slide view 'PPApp.ActiveWindow.ViewType = ppViewSlide For iCht = 1 To ActiveWorkbook.Charts.Count ' Copy chartsheet as a picture ActiveWorkbook.Charts(iCht).CopyPicture _ Appearance:=xlScreen, Size:=xlScreen, Format:=xlBitmap '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 ' position the chart With PPApp.ActiveWindow.Selection.ShapeRange .Top = 94 ' points .Left = 58 ' points .Width = 8.2 * 72 .Height = 5.6 * 72 End With End With Next ' Clean up Set PPSlide = Nothing Set PPPres = Nothing Set PPApp = Nothing End Sub |
#3
|
|||
|
|||
Multiple Excel Charts to PowerPoint 2
I am using Excel 2000 with ppt 2003 under XP.
This is a strange mix but we should align on Office '07 later this year. Thanks for the tips. Steve |
#4
|
|||
|
|||
Multiple Excel Charts to PowerPoint 2
Oh ... and when I run the macro there is no sign that there is a
problem... except that PPT never opens and there is no resulting ppt file. Steve |
#5
|
|||
|
|||
Multiple Excel Charts to PowerPoint 2
Hi,
Some of the examples code do not include the line to make the powerpoint application visible. Automation will not work for PP unless the applicaition is visible. ' Create instance of PowerPoint Set PPApp = CreateObject("Powerpoint.Application") PPApp.visible = True Cheers Andy Jon Peltier wrote: You neglected to mention which version of Office you're using. Bitmaps are only good if they are not resized and not antialiased. Their resolution is based on the screen: one screen pixel = one bitmap pixel. Until 2007, the Picture format was better, but now it has been degraded, but it at least doesn't change in quality as it is resized. I don't know why PowerPoint won't start using CreateObject. What feedback does your machine provide? You should be able to define the PowerPoint template to use. If it's anything like Excel, something like this should work: Set PPPres = PPApp.Presentations.Add("TemplatePathAndFile.pot") I'll leave it up to you to look it up in the PowerPoint object model (use the Object Browser in the VB Editor). When you get your image into PowerPoint, the dimensions you use in VBA for positioning are points. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "SteveG" wrote in message ... I am following on from an older post http://groups.google.com/group/micro...af87f70b6657f? I have used Jon Peltier's page http://peltiertech.com/Excel/XL_PPT.html#chartppt to try and create a macro that will open a new PPT, copy all th echartsheets and paste as bitmap (to stop people falsifying charts!) one to a ppt slide. My problems a 1) The macro only seems to work if PPT is already running (despite using Jon's code for creating a New PPT object) 2) The bitmap solution is ok when on screen but I get people asking if their eyes are playing tricks when we look at the printed copy. Yes it is a bit fuzzy! What controls the resolution of a bitmap? Is it the screen resolution? Is there any way to prevent ungrouping of a picture? 3) Can I define the PPT template (.pot) that should be used? 4) What decides the dimensions (points, inches, cm) used for positioning? - Are they set by the Windows regional settings? The code is below (you can see where I have added sections - extra indent) I am so close to my goal but am stuck with these last few points! Can anyone help? Steve 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 ' Create instance of PowerPoint Set PPApp = CreateObject("Powerpoint.Application") ' Create a presentation Set PPPres = PPApp.Presentations.Add ' Some PowerPoint actions work best in normal slide view PPApp.ActiveWindow.ViewType = ppViewSlide ' Add first slide to presentation Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly) ' Reference existing instance of PowerPoint 'Set PPApp = GetObject(, "Powerpoint.Application") ' Reference active presentation 'Set PPPres = PPApp.ActivePresentation ' Some PowerPoint actions work best in normal slide view 'PPApp.ActiveWindow.ViewType = ppViewSlide For iCht = 1 To ActiveWorkbook.Charts.Count ' Copy chartsheet as a picture ActiveWorkbook.Charts(iCht).CopyPicture _ Appearance:=xlScreen, Size:=xlScreen, Format:=xlBitmap '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 ' position the chart With PPApp.ActiveWindow.Selection.ShapeRange .Top = 94 ' points .Left = 58 ' points .Width = 8.2 * 72 .Height = 5.6 * 72 End With End With Next ' Clean up Set PPSlide = Nothing Set PPPres = Nothing Set PPApp = Nothing End Sub -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#6
|
|||
|
|||
Multiple Excel Charts to PowerPoint 2
Oh, good point. Slipped my mind.
I've updated my web page to reflect this. http://peltiertech.com/Excel/XL_PPT.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Andy Pope" wrote in message ... Hi, Some of the examples code do not include the line to make the powerpoint application visible. Automation will not work for PP unless the applicaition is visible. ' Create instance of PowerPoint Set PPApp = CreateObject("Powerpoint.Application") PPApp.visible = True Cheers Andy Jon Peltier wrote: You neglected to mention which version of Office you're using. Bitmaps are only good if they are not resized and not antialiased. Their resolution is based on the screen: one screen pixel = one bitmap pixel. Until 2007, the Picture format was better, but now it has been degraded, but it at least doesn't change in quality as it is resized. I don't know why PowerPoint won't start using CreateObject. What feedback does your machine provide? You should be able to define the PowerPoint template to use. If it's anything like Excel, something like this should work: Set PPPres = PPApp.Presentations.Add("TemplatePathAndFile.pot") I'll leave it up to you to look it up in the PowerPoint object model (use the Object Browser in the VB Editor). When you get your image into PowerPoint, the dimensions you use in VBA for positioning are points. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "SteveG" wrote in message ... I am following on from an older post http://groups.google.com/group/micro...af87f70b6657f? I have used Jon Peltier's page http://peltiertech.com/Excel/XL_PPT.html#chartppt to try and create a macro that will open a new PPT, copy all th echartsheets and paste as bitmap (to stop people falsifying charts!) one to a ppt slide. My problems a 1) The macro only seems to work if PPT is already running (despite using Jon's code for creating a New PPT object) 2) The bitmap solution is ok when on screen but I get people asking if their eyes are playing tricks when we look at the printed copy. Yes it is a bit fuzzy! What controls the resolution of a bitmap? Is it the screen resolution? Is there any way to prevent ungrouping of a picture? 3) Can I define the PPT template (.pot) that should be used? 4) What decides the dimensions (points, inches, cm) used for positioning? - Are they set by the Windows regional settings? The code is below (you can see where I have added sections - extra indent) I am so close to my goal but am stuck with these last few points! Can anyone help? Steve 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 ' Create instance of PowerPoint Set PPApp = CreateObject("Powerpoint.Application") ' Create a presentation Set PPPres = PPApp.Presentations.Add ' Some PowerPoint actions work best in normal slide view PPApp.ActiveWindow.ViewType = ppViewSlide ' Add first slide to presentation Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly) ' Reference existing instance of PowerPoint 'Set PPApp = GetObject(, "Powerpoint.Application") ' Reference active presentation 'Set PPPres = PPApp.ActivePresentation ' Some PowerPoint actions work best in normal slide view 'PPApp.ActiveWindow.ViewType = ppViewSlide For iCht = 1 To ActiveWorkbook.Charts.Count ' Copy chartsheet as a picture ActiveWorkbook.Charts(iCht).CopyPicture _ Appearance:=xlScreen, Size:=xlScreen, Format:=xlBitmap '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 ' position the chart With PPApp.ActiveWindow.Selection.ShapeRange .Top = 94 ' points .Left = 58 ' points .Width = 8.2 * 72 .Height = 5.6 * 72 End With End With Next ' Clean up Set PPSlide = Nothing Set PPPres = Nothing Set PPApp = Nothing End Sub -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#7
|
|||
|
|||
Multiple Excel Charts to PowerPoint 2
.... and now it works!
Thanks! Steve |
#8
|
|||
|
|||
Multiple Excel Charts to PowerPoint 2
Bitmaps are only good if they are not resized and not antialiased. Their resolution is based on the screen: one screen pixel = one bitmap pixel.
If I understand you correctly I should 'pre-set' the size of the chart so that BitMap doesn't have to be resized in PPT. To me this means that I should NOT select ViewSized to window but rather work out what zoom % is required to end-up with the correct sized image in PPT and select this by using ViewZoomCustom X%? Do I have to do anything with the size or appearance in teh VBA? How do I avoid anti-aliasing? What action would cause this as I paste images in PPT? (Clearly I do not know what I am doing!) Until 2007, the Picture format was better, but now it has been degraded, but it at least doesn't change in quality as it is resized. When you say the picture format was better do you mean Excel 2003 was better than Excel2007 or are you saying picture was better than bitmap prior to Excel 2007? I think there was something in the reader's comments on your blog but I can find it now. I am in a situation where people have taken my PPT slides and modified the data to suit their own story (there has to be a Dilbert in this!) As a result I have been using bitmaps pasted in PPT. If you are saying that when we get Excel2007 I will need to change techniques then I had better start now! Thanks for your light in the darkness! Steve |
#9
|
|||
|
|||
Multiple Excel Charts to PowerPoint 2
wrote in message ... Bitmaps are only good if they are not resized and not antialiased. Their resolution is based on the screen: one screen pixel = one bitmap pixel. If I understand you correctly I should 'pre-set' the size of the chart so that BitMap doesn't have to be resized in PPT. To me this means that I should NOT select ViewSized to window but rather work out what zoom % is required to end-up with the correct sized image in PPT and select this by using ViewZoomCustom X%? Do I have to do anything with the size or appearance in teh VBA? Instead of using standalone chart sheets, embed your chart on a worksheet. Keep window zoom at 100, and resize the chart as you would any shape. This makes sizing much easier. PowerPoint (2007 and 2003) does some internal processing of the image so that resizing a bitmap isn't as traumatic as it once was. How do I avoid anti-aliasing? What action would cause this as I paste images in PPT? (Clearly I do not know what I am doing!) I think 2007 automatically carries out antialiasing. I don't know how to prevent it, as I haven't worked that much in 2007. Until 2007, the Picture format was better, but now it has been degraded, but it at least doesn't change in quality as it is resized. When you say the picture format was better do you mean Excel 2003 was better than Excel2007 or are you saying picture was better than bitmap prior to Excel 2007? I think there was something in the reader's comments on your blog but I can find it now. Yes to both parts. It might be that BMP is prettyy much the same. I am in a situation where people have taken my PPT slides and modified the data to suit their own story (there has to be a Dilbert in this!) As a result I have been using bitmaps pasted in PPT. If you are saying that when we get Excel2007 I will need to change techniques then I had better start now! If you're pasting bitmaps, and the results are acceptable, keep pasting bitmaps. I understand the issue with people fudging the data. It describes a former situation I was involved in where even so-called certified QA data was untrustworthy. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ |
Thread Tools | |
Display Modes | |
|
|