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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
  1. 365
Platform
  1. Windows
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Apologies - am buy elsewhere for a few days - have not forgotten you
 

Forum statistics

Threads
1,148,334
Messages
5,746,162
Members
423,997
Latest member
moofish87

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
Top