Extract Excel dashboard and paste image into existing ppt using VBA

[Monty1010]

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

 

[tonyyy]

Monty1010,

Welcome to the Board.

You might consider the following...

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