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:
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:
To:
... 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?
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?