CODEWITHSUNDEEP
excelvbapowerpoint
Nikolas
Nikolas

Reputation: 37

Copy and Paste charts to slide in VBA

I'm creating a VBA code to copy a chart of a specific worksheet of Excel Workbook and paste it into a Power Point slide. Here's my code:

I'm getting an "runtime error '9' Subscript out of range" on ''Windows("FilenameExcel").Activate'' but I don't know what I'm doing wrong.

How do I solve the problem?

Sub copyPastePPT()

Dim MyPPT As Object
Dim xChart As Excel.ChartObject
Set MyPPT = CreateObject("Powerpoint.application")
Set myXLS = CreateObject("Excel.application")

FilenamePPT = OpenFileDialogPPT() 'Function to browse to a Power Point Presentation
FilenameExcel = OpenFileDialogXLS() 'Function to browse to a Excel Workbook

MyPPT.presentations.Open FilenamePPT
Workbooks.Open FilenameExcel


    Windows("FilenameExcel").Activate
    Sheets("Breakdown New").Select

    ActiveSheet.ChartObjects.Select
    ActiveChart.ChartArea.Copy

    'Copy chart to a specific slide

    Windows("FilenamePPT").Activate
    MyPPT.ActiveWindow.View.GotoSlide (3)
    MyPPT.ActivePresentation.Slides(3).Select
    MyPPT.CommandBars.ExecuteMso ("PasteSourceFormatting")
    MyPPT.CommandBars.ReleaseFocus



End Sub

Upvotes: 1

Views: 563

Answers (1)

AAA
AAA

Reputation: 3670

Sub copyPastePPT()

Dim MyPPT As Object, wb, pp
Dim xChart As Excel.ChartObject
Set MyPPT = CreateObject("Powerpoint.application")
Set myXLS = CreateObject("Excel.application")

FilenamePPT = OpenFileDialogPPT() 'Function to browse to a Power Point Presentation
FilenameExcel = OpenFileDialogXLS() 'Function to browse to a Excel Workbook

Set pp = MyPPT.presentations.Open(FilenamePPT)
Set wb = Workbooks.Open(FilenameExcel)

pp.Sheets("Breakdown New").ChartObjects(1).Chart.ChartArea.Copy

'Copy chart to a specific slide

pp.Slides(3).Select
MyPPT.CommandBars.ExecuteMso ("PasteSourceFormatting")
MyPPT.CommandBars.ReleaseFocus

End Sub

Upvotes: 1

Related Questions