1

I currently have a powerpoint with a chart that was generated through an excel.

What I need to do is get the values of the chart (or the excel, doesn't matter) in order to do some animations.

The problem is that I can't seem to get my code to work.

If there is ANY easier way to do this I will be glad to hear it!

Here's my code:

Sub moveRectangle()
Dim pptChart As Chart
Dim pptcd As ChartData
Dim xlWorkbook As Object
Dim PPPres As Presentation
Dim pptShape2 As Shape
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim sld As Slide
Dim shp As Shape
Dim PPApp As PowerPoint.Application
 'Look for existing instance
On Error Resume Next
Set PPApp = GetObject(, "PowerPoint.Application")
On Error GoTo 0
 'Create new instance if no instance exists
Set PPApp = CreateObject("Powerpoint.Application")
Set PPPres = PPApp.ActivePresentation
Set pptShape = PPPres.Slides(1).Shapes("Rectangle 16")
Set pptShape2 = PPPres.Slides(1).Shapes("Chart 3")
Set pptChart = pptShape2.Chart
Set pptcd = pptChart.ChartData
MsgBox (pptShape2.Name)
Set wb = pptcd.Workbook
Set ws = wb.Worksheets(1)
pptShape.Left = pptShape.Left - 40
End Sub

The problem is that I'm getting the following error: 

Method 'Workbook' of Object 'ChartData' failed

Any help is greately appreciated!

Waclock
  • 1,686
  • 19
  • 37
  • I'm using Office 2010 – Waclock Oct 28 '13 at 00:41
  • 1
    See this [article](http://answers.microsoft.com/en-us/office/forum/office_2007-customize/how-to-read-then-replace-data-in-powerpoint-2007/ed71dc25-41ec-4649-bb53-4e116adfe62a) about activating excel first. – Automate This Oct 28 '13 at 00:59
  • I've managed to get it to work "activating it", however now it appears over my powerpoint. Is there anyway to prevent this? – Waclock Oct 28 '13 at 01:04
  • I meant my Excel is over my Powerpoint (I don't want to see my Excel, just the presentation) – Waclock Oct 28 '13 at 01:09
  • Thanks, you could add an answer and I'll mark you as the best answer =) – Waclock Oct 28 '13 at 01:15

1 Answers1

0

In order to get it working without "activating" excel (which exists full screen mode, pretty annoying), what must be done is adding 

With pptChart.ChartData
...
End With

This allows you to get the same functionalities without having to "activate excel"

Waclock
  • 1,686
  • 19
  • 37
  • You say you're able to access the `ChartData.Workbook` properties and methods without explicitly calling the `ChartData.Activate` method, but this does not seem to be actually true. Can you elaborate? – David Zemens Apr 25 '17 at 12:31
  • @DavidZemens this was 4 years ago...can't really recall. – Waclock Apr 25 '17 at 18:26
  • Yes I realize that it was quite a long time ago -- just a shot in the dark. Everything I read, everywhere, says that this is explicitly impossible, and my own tests & work over the past 4 years support that. I was just hoping *maybe* there was some special thing you did which actually made this work... :) Thanks for replying anyways though! – David Zemens Apr 25 '17 at 18:33
  • @DavidZemens no problem; I doubt I did anything special though, this was when I was in college and didn't have any experience with VBA (still don't haha) – Waclock Apr 25 '17 at 18:35
  • I tried right now, it looks like this answer is totally wrong. – Kar.ma Mar 01 '18 at 10:06