HI All,
I'm attempting to write some VBA that will take named ranges/tables/charts in Excel and copy those over to PowerPoint. This is a report I would update each month, so code will copy from a template and replace the existing named objects in PowerPoint. (That's the idea anyway) I have found the names of the selected shapes in the PPT in the Selection Pane and I'm wondering if those references can be used in VBA coding to paste. Ideally, using those named object references would replace the shape in the exact location it already is, but perhaps that is wishful thinking and I will have to get granular with setting locations on each shape w/ the Top, Height, Width, Left descriptions in PPT slide. The 'Paste over existing shape is my trouble spot. It will paste the range in the center of the slide. Perhaps it's not possible to replace the shape and I will need to delete existing shape and paste & Position the Excel Range.
Thank you for taking the time to read and advise!
Sub openppt()
'Declare Variables
Dim papp As PowerPoint.Application
Dim ppt As PowerPoint.Presentation
Dim psld As PowerPoint.slide
Dim Perf As Range
Dim shp As PowerPoint.Shape
'create a new instance of powerpoint and Open Presentation
Set papp = New PowerPoint.Application
Set ppt = papp.Presentations.Open("Location\on\my\drive.pptx")
'Set Slide in Powerpoint
Set psld = ppt.Slides(3)
Set shp = psld.Shapes("Can this be a name from the Selection Pane?")
'Copy Range from Excel
Set Perf = Worksheets("Perf Summary").Range("perf")
Perf.Copy
'Pause script for one second
Application.Wait Now + #12:00:01 AM#
'Paste over existing shape
ppt.psld.shp.paste
Set psld = Nothing
Set shp = Nothing
End Sub
I'm attempting to write some VBA that will take named ranges/tables/charts in Excel and copy those over to PowerPoint. This is a report I would update each month, so code will copy from a template and replace the existing named objects in PowerPoint. (That's the idea anyway) I have found the names of the selected shapes in the PPT in the Selection Pane and I'm wondering if those references can be used in VBA coding to paste. Ideally, using those named object references would replace the shape in the exact location it already is, but perhaps that is wishful thinking and I will have to get granular with setting locations on each shape w/ the Top, Height, Width, Left descriptions in PPT slide. The 'Paste over existing shape is my trouble spot. It will paste the range in the center of the slide. Perhaps it's not possible to replace the shape and I will need to delete existing shape and paste & Position the Excel Range.
Thank you for taking the time to read and advise!
Sub openppt()
'Declare Variables
Dim papp As PowerPoint.Application
Dim ppt As PowerPoint.Presentation
Dim psld As PowerPoint.slide
Dim Perf As Range
Dim shp As PowerPoint.Shape
'create a new instance of powerpoint and Open Presentation
Set papp = New PowerPoint.Application
Set ppt = papp.Presentations.Open("Location\on\my\drive.pptx")
'Set Slide in Powerpoint
Set psld = ppt.Slides(3)
Set shp = psld.Shapes("Can this be a name from the Selection Pane?")
'Copy Range from Excel
Set Perf = Worksheets("Perf Summary").Range("perf")
Perf.Copy
'Pause script for one second
Application.Wait Now + #12:00:01 AM#
'Paste over existing shape
ppt.psld.shp.paste
Set psld = Nothing
Set shp = Nothing
End Sub
