VBA - Edit PPT Charts from Excel Not Working

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Hello,

I've been using this same code for years but for some reason it isn't working anymore. Not sure if it has to do with a PPT update and how the chart objects work.

Here's my code:
VBA Code:
    Set copiedRange = myWorksheet.Range(myWorksheet.Cells(6, firstColumn), myWorksheet.Cells(lastRow, lastColumn))
    copiedRange.Copy

    Set myChart = pptPres.Slides(sld).Shapes(shp).Chart
    myChart.ChartData.Activate

    Set wb = myChart.ChartData.Workbook
    Set ws = wb.Worksheets(1)

    ws.Range("A1").PasteSpecial


    'close and reopen so it sticks
    wb.Close
    DoEvents
    myChart.ChartData.Activate
    myChart.ChartData.Workbook.Close

The sld and shp variables are from elsewhere in my program and are working fine. I've used a debug.print to print myChart.name and it is coming back correctly.

At first, my copied range was formulas in my Excel file, and I was getting errors on ws.Range("A1").PasteSpecial Paste:=xlPasteValues. So, thinking that was the issue, I pasted all my formulas as values in my source file and then updated the paste line to just ws.Range("A1").PasteSpecial

But I'm still getting errors.

When I run the above code I get a Method 'Activate' of object 'ChartData' failed, but it happens on the second mychart.ChartData.Activate line. It doesn't have an error on the first instance of that code. Occasionally when I run, I get an error, and then I go back to the PPT chart to try and edit data and it gives me an error that the linked data can't be found.

If I change this line:
VBA Code:
  ws.Range("A1").PasteSpecial

To:
VBA Code:
  ws.Range("A1").PasteSpecial xlPasteValues

... I don't get an error anymore but the charts don't update.

Did PPT change the way to access charts via VBA? When I right click the chart and go to 'Edit Data', the Excel that pulls up isn't exactly like it used to be. There's no worksheet tabs, no menu, etc. If I click on 'Edit data in Excel' it just brings up a blank Excel window with no workbook in it.

Is using ChartData.Workbook.Worksheets(1) not valid anymore?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Rich (BB code):
Set myChart = pptPres.Slides(sld).Shapes(shp).Chart

How is variable myChart declared in the code ?

Please update your user profile - Office Version?? and Platform?? - thanks
 
Upvote 0
Rich (BB code):
Set myChart = pptPres.Slides(sld).Shapes(shp).Chart

How is variable myChart declared in the code ?

Please update your user profile - Office Version?? and Platform?? - thanks

Sorry about that.

Updated my profile. I'm using Office 365 on Windows 10 64-bit.

I had used this code on previous versions of Excel (I believe up to 2016, but def 2013).

These are my declarations:
VBA Code:
Dim ppt As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim myChart As PowerPoint.Chart
Dim myTable As PowerPoint.Table

And I have the Reference to the PPT Object Library checked.
 
Upvote 0
Thanks
Helpful to know that you are also on 365
I will test later and see how everything runs for me
Will update the thread tomorrow
 
Upvote 0
Apologies - am buy elsewhere for a few days - have not forgotten you
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top