Code works in debug mode but fails to run in compile mode

[rohanmalhotra1983]

Hi all

I am writing a VBA code to copy charts from Excel to Powerpoint. The code is running fine in debug mode, however, gives an error in compile mode. Can anyone please help?

Thanks in advance.

MBSheet.Activate
MBSheet.ChartObjects("Chart 1").Chart.ChartArea.Copy

mySlide.Select
With mySlide
     .Application.CommandBars.ExecuteMso ("PasteExcelChartSourceFormatting")
     With .Application.ActiveWindow.Selection.ShapeRange
        .Top = Application.CentimetersToPoints(3.9)
        .Left = Application.CentimetersToPoints(1.57)
        .Height = Application.CentimetersToPoints(11.93)
        .Width = Application.CentimetersToPoints(10.96)
    End With
End With

 

[Trevor G]

Can you show all your code and let us know which line of code it fails on.

 

[rohanmalhotra1983]

Hi Trevor

below is the code

Sub GlobalMoneyBall()

Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As PowerPoint.Slide
Dim myShape As Object
Dim MainWB As Workbook
Dim MBSheet As Worksheet

Application.DisplayAlerts = False
'*****************Open Excel file where charts are saved*********************
Workbooks.Open Filename:="J:\Research\Internal\Moneyball.xlsx"
Set MainWB = Workbooks("Moneyball.xlsx")
Set MBSheet = MainWB.Sheets("Global charts")
'*****************Open Powerpoint where charts are to be saved********************
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
Set myPresentation = PowerPointApp.Presentations.Open(Filename:="J:\Research\Internal\Internal Report.pptx")
Set mySlide = myPresentation.Slides(4)
MBSheet.Activate
MBSheet.ChartObjects("Chart 1").Chart.ChartArea.Copy
mySlide.Select
With mySlide
     .Application.CommandBars.ExecuteMso ("PasteExcelChartSourceFormatting")
     With .Application.ActiveWindow.Selection.ShapeRange
        .Top = Application.CentimetersToPoints(3.9)
        .Left = Application.CentimetersToPoints(1.57)
        .Height = Application.CentimetersToPoints(11.93)
        .Width = Application.CentimetersToPoints(10.96)
    End With
End With

MBSheet.Activate
MBSheet.ChartObjects("Chart 2").Chart.ChartArea.Copy
mySlide.Select
With mySlide
     .Application.CommandBars.ExecuteMso ("PasteExcelChartSourceFormatting")
     With .Application.ActiveWindow.Selection.ShapeRange
        .Top = Application.CentimetersToPoints(3.9)
        .Left = Application.CentimetersToPoints(13.2)
        .Height = Application.CentimetersToPoints(11.93)
        .Width = Application.CentimetersToPoints(10.96)
    End With
End With
Application.DisplayAlerts = True
End Sub

it is giving me error on With .Application.ActiveWindow.Selection.ShapeRange

Thanks
Rohan

 

[Norie]

Rohan

What's the error message/description?

 

[jim may]

Remove only the first "." and rerun.

 

[rohanmalhotra1983]

Hi Norie

I am getting the following error:

Method 'ShapeRange' of object 'Selection' failed.

Thanks
Rohan

 

[Norie]

Rohan

What is it you are trying to do with the slide in your PowerPoint presentation?

 

[rohanmalhotra1983]

Norie

I am trying to copy a chart from excel and paste it in PowerPoint. I don't want to paste as picture.

Rohan

 

[Norie]

If that's all then it might be the case that you don't actually need the code that's causing the problem.

Try this.

Sub GlobalMoneyBall()

Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As PowerPoint.Slide
Dim myShape As Object
Dim MainWB As Workbook
Dim MBSheet As Worksheet

    Application.DisplayAlerts = False
    '*****************Open Excel file where charts are saved*********************
    Workbooks.Open Filename:="J:\Research\Internal\Moneyball.xlsx"
    Set MainWB = Workbooks("Moneyball.xlsx")
    Set MBSheet = MainWB.Sheets("Global charts")
    
    '*****************Open Powerpoint where charts are to be saved********************
    If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")

    Set myPresentation = PowerPointApp.Presentations.Open(Filename:="J:\Research\Internal\Internal Report.pptx")
    Set mySlide = myPresentation.Slides(4)
    
    MBSheet.Activate
    MBSheet.ChartObjects("Chart 1").Chart.ChartArea.Copy
    
    mySlide.Select
    mySlide.Application.CommandBars.ExecuteMso ("PasteExcelChartSourceFormatting")
     
    MBSheet.Activate
    MBSheet.ChartObjects("Chart 2").Chart.ChartArea.Copy
    
    mySlide.Select
    mySlide.Application.CommandBars.ExecuteMso ("PasteExcelChartSourceFormatting")

    Application.DisplayAlerts = True
    
End Sub

 

[rohanmalhotra1983]

Norie

I would need the code as I have to position the chart on that slide. The slide will have 2 charts on the same slide with equal height and width.

Thanks