Paste Excel Charts to PPT Dynamically using chart name

[abc_xyz]

Trying to create a dashboard wherein the user can add details regarding the File Name, Sheet Name, Chart Name, Slide No and many more.
The below code gives an error when it tries to copy a chart from the excel sheet (Subscription Out of Range). Not sure where am I going wrong. Can anyone help?

Area of error -
With FileToOpen

Set Chart = Worksheets("OpenBook_Sheet").ChartObjects("OpenBook_Charts")

Chart.CopyPicture Appearance:=xlScreen, Format:=xlPicture

End With

Sub Excel_Charts_To_PPT()
    
Dim OpenBook_path As String, OpenBook_Sheet, OpenBook_Charts, Available_File As String, Next_File As String
Dim Chart As ChartObjects
Dim FileToOpen As Workbook
Dim i As Long, Lastrow As Long
Dim FSO As Object
Dim StartTime As Double
Dim MinutesElapsed As String


StartTime = Timer


With Application
   .ScreenUpdating = False
   .DisplayAlerts = False
   .EnableEvents = False
End With
    
    
    
    
pptTemplate = ThisWorkbook.Sheets("Dashboard_Charts").Range("F3").Value

Set PPT = CreateObject("Powerpoint.Application")

PPT.Presentations.Open Filename:=pptTemplate
    
    
    
    
Lastrow = ThisWorkbook.Sheets("Dashboard_Charts").Range("F" & Rows.Count).End(xlUp).Row
    
    For i = 9 To Lastrow
        
        OpenBook_path = ThisWorkbook.Sheets("Dashboard_Charts").Cells(i, 6)  'Path includes file name with extension
        OpenBook_Sheet = ThisWorkbook.Sheets("Dashboard_Charts").Cells(i, 7)
        OpenBook_Charts = ThisWorkbook.Sheets("Dashboard_Charts").Cells(i, 8).Value
        
        Slide_No = ThisWorkbook.Sheets("Dashboard_Charts").Cells(i, 9)
        Img_Height = ThisWorkbook.Sheets("Dashboard_Charts").Cells(i, 10)
        Img_Width = ThisWorkbook.Sheets("Dashboard_Charts").Cells(i, 11)
        Img_Horizontal_Pos = ThisWorkbook.Sheets("Dashboard_Charts").Cells(i, 12)
        Img_Vetical_Pos = ThisWorkbook.Sheets("Dashboard_Charts").Cells(i, 13)
        
        
        
'Check if file is open,if open, then use open file; if not, open file from the path in the cell
        Available_File = Dir(OpenBook_path) 'extracts the file name from the path
        
        Set FileToOpen = Nothing
        If Not wbOpen(Available_File, FileToOpen) Then
            Set FileToOpen = Workbooks.Open(OpenBook_path)
        End If
        
        
        
'open workbook from the path in the cell
        With FileToOpen
        
        Set Chart = Worksheets("OpenBook_Sheet").ChartObjects("OpenBook_Charts")
            
                Chart.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        
        End With
        
        
        
'Paste Excel range in slide with dimensions
        With PPT.ActivePresentation.Slides(Slide_No)
             .Shapes.PasteSpecial
                With .Shapes(.Shapes.Count)
                     .LockAspectRatio = msoFalse
                     .Height = Img_Height * 28.3465
                     .Width = Img_Width * 28.3465
                     .Left = Img_Horizontal_Pos * 28.3465
                     .Top = Img_Vetical_Pos * 28.3465
                End With
        End With
        
        
        
        
 'Check if Below File Path & Name are same
        Next_File = ThisWorkbook.Sheets("Dashboard_Charts").Cells(i + 1, 6) 'extracts the file name from the path
        If Trim(Next_File) = "" Then
            Exit For
        Else
            If OpenBook_path = Next_File Then
                
            Else
                On Error Resume Next
                FileToOpen.Activate
                FileToOpen.Close False
                Set FileToOpen = Nothing
                On Error GoTo 0
            End If
        End If
    Next i
    On Error Resume Next
    FileToOpen.Close False
    
    
    ThisWorkbook.Sheets("Dashboard_Charts").Activate
    
    
With Application
   .ScreenUpdating = True
   .DisplayAlerts = True
   .EnableEvents = True
End With
    
    
    Successful_Msg
    PPT.Visible = True
    
    
'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
    
    
    
End Sub



Function wbOpen(wbName As String, wbO As Workbook) As Boolean
    On Error Resume Next
    Set wbO = Workbooks(wbName)
    wbOpen = Not wbO Is Nothing
End Function

 

[Domenic]

Your variable Chart is being assigned a single ChartObject object. So it should be declared as follows...

Dim Chart As ChartObject

Hope this helps!