VBA: Can't get "xlPastevaluesAndNumberFormats" to work

user_11315351 0
Viewed 347

I have been trying to copy a range from an excel and paste it to the embedded excel of a native chart in PPT using xlPastevaluesAndNumberFormats of PasteSpecial method. For some reason it is not working. Could somebody help me understand what I am getting wrong.

Sample code and error pic below:

 Private Sub UpdateSlide(targetslide As PowerPoint.Slide, thatsheet As Worksheet)

 Dim StoreArray As Variant
 Dim aShp As PowerPoint.Shape
 Dim rng As Range
 Set rng = thatsheet.Range("A1").CurrentRegion
 rng.Copy
' StoreArray = rng   
 
 For Each aShp In targetslide.Shapes
      If aShp.HasChart Then
        With aShp.Chart.ChartData
               Dim del_rng As Range
               Set del_rng = .Workbook.Sheets(1).Range("A1") '.CurrentRegion
               'del_rng.Resize(UBound(StoreArray, 1), UBound(StoreArray, 2)) = StoreArray  'This option works but screws up formatting
               del_rng.PasteSpecial (xlPastevaluesAndNumberFormats) 'Fails here

enter image description here

powerpoint vba excel
1 Answers

Further to what I have mentioned in THIS post, you need to activate the backend Excel workbook before you can paste to native powerpoint chart (Insert | Chart) workbook else you will get the error as shown below.

enter image description here

To activate the backend excel book, use

aShp.Chart.ChartData.ActivateChartDataWindow

Combining both suggestions, your code can be written as

Private Sub UpdateSlide(targetslide As PowerPoint.Slide, thatsheet As Worksheet)
    Dim StoreArray As Variant
    Dim aShp As PowerPoint.Shape
    
    Dim wb As Workbook
    Dim chartsheet As Worksheet
    Dim del_rng As Range
    Dim rng As Range
    
    Set rng = thatsheet.Range("A1").CurrentRegion

    For Each aShp In targetslide.Shapes
        If aShp.HasChart Then
            With aShp.Chart.ChartData
                Set wb = .Workbook
                Set chartsheet = wb.Sheets(1)
                Set del_rng = chartsheet.Range("A1")
                
                aShp.Chart.ChartData.ActivateChartDataWindow

                rng.Copy
                DoEvents
                del_rng.PasteSpecial (xlPasteValuesAndNumberFormats)

                row_1 = .Workbook.Sheets(1).Range("A100").End(xlUp).Row
                col_1 = .Workbook.Sheets(1).Range("AZ1").End(xlToLeft).Column
                
                Set chartsheet = Nothing
                Set del_rng = Nothing
                
                wb.Close (True)

                Set wb = Nothing
            End With

            With aShp
                .Chart.SetSourceData _
                Source:="='Sheet1'!$A$1:" & .Chart.ChartData.Workbook.Sheets(1).Cells(row_1, col_1).Address
            End With
        End If
    Next
End Sub
user_11405790
Related