VBA copy to PPT issues between different Office versions

[Rastaman]

I have the code below that works perfectly in Office 2013. It opens an embedded ppt file and copies two charts and one data range to slide 2 of the ppt file. This Excel file is shared with multiple users, however, some of which have office 2010 or office 2007. In both of those office versions the macro runs without error, but it does not paste in the Excel range.

I found most of this code here and on Peltier's site. I believe I've set it up as non-binding to avoid version issues. However, you'll notice I added error checking as shown here during my troubleshooting.



<font face=Courier New>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    PPSlide.Shapes.Paste.Select<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0</FONT>


The older office versions fail on this paste command, stating that either nothing has been copied or object doesn't support command. However, if while in Office 2010 I stop the code just before this paste I can see that the Excel range is copied, and I can manually paste to PPT. Note that I want the pasted range to be an editable object. I do not want to paste a picture of the range. Peltier's examples that I found only paste as a picture so I modified the copy command to just Copy instead of CopyPicture.

Thanks.


Full code is as follows:


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> BuildReport()<br><br><SPAN style="color:#007F00">'open the PowerPoint file</SPAN><br>    Sheets("Report").Select<br>    ActiveSheet.Shapes.Range(Array("CostTemplate")).Select<br>    Selection.Verb Verb:=xlOpen<br>    <br><SPAN style="color:#007F00">'Call each routine, passing the name of the worksheet to copy graph and slide number the graph is to be pasted in</SPAN><br>Copy_Chart1 "Report", 2<br>Copy_Chart2 "Report", 2<br>Copy_Range "Report", 2<br><br>Range("C3").Select<br>Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br><br>MsgBox "PowerPoint slide updated successfully. *Save As* the presentation to your computer."<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> Copy_Chart1(sheet, slide)<br><br><SPAN style="color:#00007F">Dim</SPAN> PPApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> PPPres <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> PPSlide <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> PPApp = GetObject(, "Powerpoint.Application")<br><SPAN style="color:#00007F">Set</SPAN> PPPres = PPApp.ActivePresentation<br><br>PPApp.ActiveWindow.ViewType = 1<br>PPApp.ActiveWindow.View.GotoSlide (slide)<br><br>Worksheets(sheet).Activate<br>ActiveSheet.ChartObjects("Chart 1").Chart.CopyPicture _<br>Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture<br><br><SPAN style="color:#00007F">Set</SPAN> PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)<br><br><SPAN style="color:#007F00">' paste and select the chart picture</SPAN><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>PPSlide.Shapes.Paste.Select<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><br><SPAN style="color:#007F00">' align the chart</SPAN><br>    PPApp.ActiveWindow.Selection.ShapeRange.LockAspectRatio = msoFalse<br>    PPApp.ActiveWindow.Selection.ShapeRange.Left = 10<br>    PPApp.ActiveWindow.Selection.ShapeRange.Top = 280<br>    PPApp.ActiveWindow.Selection.ShapeRange.Height = 200<br>    PPApp.ActiveWindow.Selection.ShapeRange.Width = 400<br><br><SPAN style="color:#007F00">' Clean up</SPAN><br><SPAN style="color:#00007F">Set</SPAN> PPSlide = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">Set</SPAN> PPPres = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">Set</SPAN> PPApp = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> Copy_Chart2(sheet, slide)<br><br><SPAN style="color:#00007F">Dim</SPAN> PPApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> PPPres <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> PPSlide <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> PPApp = GetObject(, "Powerpoint.Application")<br><SPAN style="color:#00007F">Set</SPAN> PPPres = PPApp.ActivePresentation<br><br>PPApp.ActiveWindow.ViewType = 1<br>PPApp.ActiveWindow.View.GotoSlide (slide)<br><br>Worksheets(sheet).Activate<br>ActiveSheet.ChartObjects("Chart 2").Chart.CopyPicture _<br>Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture<br><br><SPAN style="color:#00007F">Set</SPAN> PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)<br><br><SPAN style="color:#007F00">' paste and select the chart picture</SPAN><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>PPSlide.Shapes.Paste.Select<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><br><SPAN style="color:#007F00">' align the chart</SPAN><br>    PPApp.ActiveWindow.Selection.ShapeRange.LockAspectRatio = msoFalse<br>    PPApp.ActiveWindow.Selection.ShapeRange.Left = 300<br>    PPApp.ActiveWindow.Selection.ShapeRange.Top = 60<br>    PPApp.ActiveWindow.Selection.ShapeRange.Height = 200<br>    PPApp.ActiveWindow.Selection.ShapeRange.Width = 400<br><br><SPAN style="color:#007F00">' Clean up</SPAN><br><SPAN style="color:#00007F">Set</SPAN> PPSlide = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">Set</SPAN> PPPres = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">Set</SPAN> PPApp = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> Copy_Range(sheet, slide)<br><br><SPAN style="color:#00007F">Dim</SPAN> PPApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> PPPres <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> PPSlide <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> PPApp = GetObject(, "Powerpoint.Application")<br><SPAN style="color:#00007F">Set</SPAN> PPPres = PPApp.ActivePresentation<br><br>PPApp.ActiveWindow.ViewType = 1<br>PPApp.ActiveWindow.View.GotoSlide (slide)<br><br>    Worksheets(sheet).Range("N28:P39").Copy<br><br>    <SPAN style="color:#00007F">Set</SPAN> PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)<br><br>    <SPAN style="color:#007F00">' Paste the range</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    PPSlide.Shapes.Paste.Select<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <br>    <SPAN style="color:#007F00">' Align the pasted range</SPAN><br>    PPApp.ActiveWindow.Selection.ShapeRange.Left = 40<br>    PPApp.ActiveWindow.Selection.ShapeRange.Top = 45<br>        <br>    <SPAN style="color:#007F00">' Clean up</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> PPSlide = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> PPPres = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> PPApp = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><br></FONT>