VBA: Can't get "xlPastevaluesAndNumberFormats" to work

Question

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

I have answered a similar question before. Let me find the link. one moment — Siddharth Rout, Jun 24 '21 at 14:18
You are setting your range as `aShp.Chart.ChartData.Workbook.Sheets(1).Range("A1")`? — Darrell H, Jun 24 '21 at 14:20
[HERE](https://stackoverflow.com/questions/64872887/why-does-pastespecial-method-sometimes-throw-error-1004) it is. Put the line `rng.Copy` just before `del_rng.PasteSpecial (xlPasteFormulasAndNumberFormats)`. Also insert `Doevents` between those 2 lines. — Siddharth Rout, Jun 24 '21 at 14:21
@SiddharthRout Okay, Thanks lemme quickly try that out. But as mentioned the xlpasteall works fine for some reason, Any insights as to why ? — Charlie, Jun 24 '21 at 14:22
@DarrellH yes, the idea is to get data to the embedded excel of a chart. — Charlie, Jun 24 '21 at 14:23
Works fine for me in sample data. nothing wrong with the line `del_rng .PasteSpecial (xlPasteFormulasAndNumberFormats)` :) Try what I mentioned above ans see if you are still getting the error? — Siddharth Rout, Jun 24 '21 at 14:25
@SiddharthRout Hi, I tried it and now it is giving me the linked file not available error i.e the embedded excel can't be opened. Will restart and see. This ppt automation is a nightmare compared to excel though... :D — Charlie, Jun 24 '21 at 14:34
That is a different error. Your paste error is gone it seems :D — Siddharth Rout, Jun 24 '21 at 14:37
@SiddharthRout I am afraid not, moving the line inside as you suggested causes a new run time error 462 — Charlie, Jun 24 '21 at 14:53
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234167/discussion-between-siddharth-rout-and-charlie). — Siddharth Rout, Jun 24 '21 at 15:06

score 1 · Answer 1 · answered Jun 24 '21 at 16:28

1

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.

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

answered Jun 24 '21 at 16:28

Siddharth Rout

147,039
17
206
250

Thank you so much, Siddharth. It is working...... :) – Charlie Jun 24 '21 at 16:31
Anyone following this, please note that aShp.Chart.ChartData.Activate does't work. Why it doesn't work is beyond me. Let's hope some expert *cough *cough *siddharth *cough may help us out here ..... :D – Charlie Jun 24 '21 at 16:50
It does work. What error are you getting? BTW why are you using that and to achieve what purpose? – Siddharth Rout Jun 24 '21 at 18:31
Sorry, didn't get ya. I said aShp.Chart.ChartData.Activate doesn't work but aShp.Chart.ChartData.ActivateChartDataWindow works. I was asking if anybody can shed a light on as to why ? – Charlie Jun 25 '21 at 04:10
For me both lines do not give any error :) For your case however we need `ActivateChartDataWindow` – Siddharth Rout Jun 25 '21 at 05:22
Excel gods have probably blessed you. – Charlie Jun 25 '21 at 07:54
Yeah...unfortunately it isn't working on loops. Sometimes works, sometimes doesn't. Unaccepted just so if anybody has any other idea, they won't go without chiming in – Charlie Jun 26 '21 at 11:40
"It isn't working" is not a very helpful statement. You need to share more details. Also what happens if you add this one line `Doevents` after `If aShp.HasChart Then` – Siddharth Rout Jun 26 '21 at 12:24
Well It works well for individual charts, but as soon as put it to work in a loop, it starts behaving abnormally. In some cases it took all the time to execute and yet nothing was updated. – Charlie Jun 28 '21 at 04:55
Did you use DoEvents? – Siddharth Rout Jun 28 '21 at 05:03
Yes, i did. This seems like one of those vba quirks. – Charlie Jun 28 '21 at 05:22
Can yuo update the question with the final code that you are using? – Siddharth Rout Jun 28 '21 at 05:25
It is the same code that you have suggested with the latest update of inserting Do Events after ashp.haschart line. As I said works fine for individual cases, but in a loop it behaves unpredictably. So I decided to avoid paste special altogether. – Charlie Jun 28 '21 at 10:46

VBA: Can't get "xlPastevaluesAndNumberFormats" to work

1 Answers1