Named Range in Excel to replace Named Object in PPT

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,143,637
Messages
5,719,972
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top