How to Open Datasheet for Existing PowerPoint Chart

Question

I have searched extesively and do not seem to find an answer. I want to update the data in an existing powerpoint chart. So far I am able to open he presentation,select the page, but I am not able to go past the last line below.

I need to open the datasheet so I can paste the data there. I was able to do it with a new chart but then the dataseheet opens immediately, but I want to use an existign chart as it will have all formatting as required. I will then do this for multiple charts in multiple pages to update a standard presetation for a new group ofcustomers.

Can someone show how would I open the datashet so I can select the right cell and paste my new data (whihc omes from Excel). For many reasons I do not want to pasteexcel charts,I want the daa to reside in PowerPoint. This macro is run from the excel file that has the new data.

Sub test2()

Set rngData = Range("A1:D6")

Set ObjPPT = CreateObject("PowerPoint.Application")
Set ObjPresentation = ObjPPT.Presentations.Open("C:\Documents and Settings\ewnym5s\My Documents\Document_sample.pptx")
Set ObjSlide = ObjPresentation.Slides(1)
Set mychart = ObjSlide.Shapes("Chart 1").Chart
mychart.Select

Set wb = mychart.ChartData
Set ws = wb.Worksheets(1)

score 7 · Answer 1 · edited May 23 '17 at 12:07

7

To open the datasheet you have to select the shape, and then activate the chartdata. And then you can access the workbook. I've modified your code slightly with some comments

  Sub test2()

    Set rngData = Range("A1:D6")

    Set ObjPPT = CreateObject("PowerPoint.Application")
    Set ObjPresentation = ObjPPT.Presentations.Open("C:\Documents and Settings\ewnym5s\My Documents\Document_sample.pptx")
    Set ObjSlide = ObjPresentation.Slides(1)
    Set mychart = ObjSlide.Shapes("Chart 1").Chart
    mychart.Select

    'Activate the chart data
    mychart.ChartData.Activate
    'Retrieve the workbook.
    Set wb = mychart.ChartData.Workbook
    Set ws = wb.Worksheets(1)

    'Do stuff with the workbook then close with save
    wb.Close True
  end Sub

Incase you do not know this, when you activate the chartdata Excel will open to display the workbook. There is no way to avoid having it at least flicker if you use this method. To make the flicker as brief as possible you could add wb.parent.Visible = false. If you cannot stand the flicker, I indicated a possible work around in this thread, but it requires the use of OLEObjects and older controls.

edited May 23 '17 at 12:07

Community

1
1

answered Oct 10 '12 at 23:49

Daniel

12,982
3
36
60

Can u recommend a good vba book that will explain the model not only a reference? – user1617979 Oct 11 '12 at 00:26
Actually, I can't. I've never seen a VBA book that I considered to be useful. Mostly the books cover basic things, and this is a little off the beaten path. If you want to learn more about a particular method, object etc... your best resource is either the help files in the program you are using or the internet. – Daniel Oct 11 '12 at 03:00
1

It looks like you can avoid the flicker but ensuring the workbook is already open in an invisible instance of Excel before you Activate it. To do this, consider investigating the Shape.LinkFormat.SourceFullName property, and then dimming an Excel.Application instance (which will be created with visible=false), and opening the workbook in that. EDIT: No need, doing this, to activate the data. You just access it, and the built in method handles identifying where the workbook is in memory. You WILL though, need to call Shape.LinkFormat.Update() on the parent shape entity. – tobriand Sep 22 '14 at 11:38

How to Open Datasheet for Existing PowerPoint Chart

1 Answers1