Extract Excel dashboard and paste image into existing ppt using VBA

Monty1010

New Member
Joined
Aug 24, 2017
Messages
1
Hi,

I have an excel file which contains a basic dashboard that senior managers wish to see in powerpoint. I have found some VBA code from a google search that can extract the dashboard and paste it as an image in a generic new ppt file in unspecified dimensions. However I cannot for the life of me figure out how to adapt the code to open an existing file path (on my C:) and paste in the image in the correct dimensions.

Below is the code I got from the internet and would appreciate anyone who can help identify how I need to change the code so it can paste into a specified ppt file and in specified dimensions :)

Sub CopyRangeToPresentation()
'Step 1: Declare your variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim SlideTitle As String
'Step 2: Open PowerPoint and create new presentation
Set PP = New PowerPoint.Application
Set PPPres = PP.Presentations.Add
PP.Visible = True
'Step 3: Add new slide as slide 1 and set focus to it
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
PPSlide.Select
'Step 4: Copy the range as a picture
Sheets("Questions to Complete").Range("A5:N60").CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
'Step 5: Paste the picture and adjust its position
PPSlide.Shapes.Paste.Select
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
'Step 6: Add the title to the slide
SlideTitle = "My First PowerPoint Slide"
PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle
'Step 7: Memory Cleanup
PP.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set PP = Nothing
End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Monty1010,

Welcome to the Board.

You might consider the following...

Code:
Sub CopyRangeToPresentation_1020279()
    'Set reference to Microsoft PowerPoint Object Library
    'Step 1: Declare your variables
    Dim PP As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    Dim SlideTitle As String
    Dim shp As Object
    
    'Step 2: Open PowerPoint and create new presentation
    Set PP = New PowerPoint.Application
    Set PPPres = PP.Presentations.Open("C:\YourFilePath\YourFileName.pptx")
    PP.Visible = True
    
    'Step 3: Add new slide as slide 1 and set focus to it
    Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
    PPSlide.Select
    
    'Step 4: Copy the range as a picture
    Sheets("Questions to Complete").Range("A5:N60").CopyPicture _
        Appearance:=xlScreen, Format:=xlPicture

    'Step 5: Paste the picture and adjust its position
    PPSlide.Shapes.Paste.Select
    Set shp = PP.ActiveWindow.Selection.ShapeRange
    shp.Height = 250 'Change as needed
    shp.Width = 250 'Change as needed
    'Center picture on slide
    With PPPres.PageSetup
        shp.Left = (.SlideWidth / 2) - (shp.Width / 2)
        shp.Top = (.SlideHeight / 2) - (shp.Height / 2)
    End With
    
    'Step 6: Add the title to the slide
    SlideTitle = "My First PowerPoint Slide"
    PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle
    
'    Step 7: Memory Cleanup
    PP.Activate
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PP = Nothing
End Sub

Cheers,

tonyyy
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

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
Back
Top