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?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
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,871
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,871
Office Version
  1. 365
Platform
  1. Windows
Apologies - am buy elsewhere for a few days - have not forgotten you
 

Watch MrExcel Video

Forum statistics

Threads
1,114,555
Messages
5,548,727
Members
410,867
Latest member
Dhanas
Top