excel - VBA:无法让 "xlPastevaluesAndNumberFormats"工作

Question

我一直在尝试使用 从 excel 中复制一个范围并将其粘贴到 PPT 中原生图表的嵌入式 excel 中。 xlPastevaluesAndNumberFormats PasteSpecial 方法。由于某种原因，它不起作用。有人可以帮我理解我做错了什么。
示例代码和错误图片如下:

 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

Answer 1

除了我在 THIS 中提到的内容发布后，您需要激活后端 Excel 工作簿，然后才能粘贴到 native powerpoint 图表(插入 | 图表)工作簿，否则您将收到如下所示的错误。

要激活后端 excel 书，请使用

aShp.Chart.ChartData.ActivateChartDataWindow

结合这两个建议，您的代码可以写成

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