469,575 Members | 1,523 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

Extracting data from Powerpoint datasheet/MSgraph to Excel

Hi,

I'm trying to use VBA to extract underlying data from charts in powerpoint to excel, i.e from the underlying powerpoint datasheet that feeds the chart.

I've found the macro below on the net. It copies the data in each chart/datasheet and slide in powerpoint but I'm struggling with the bit to get the data into excel.

Below it I've tried incoporating some code to paste into excel but it does not seem to work.

Hope someone can help.

Thanks,

Lucas
Expand|Select|Wrap|Line Numbers
  1. Sub GetChartData1() 'copies data from sheet
  2. Dim s As Shape 'gr As Graph.Chart
  3. Dim gr As Object
  4. Dim sl As Slide
  5. 'Copies data from datasheet in powerpoint
  6. For Each sl In ActivePresentation.Slides
  7. For Each s In sl.Shapes
  8. If s.Type = msoEmbeddedOLEObject Then
  9. 'we have found an OLE object
  10. 'check if it's a graph
  11. If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
  12. 'this might vary depending on what version you're using
  13. 'now get a handle on the graph object itself
  14. Set gr = s.OLEFormat.Object
  15. gr.Application.DataSheet.Cells.Copy
  16.  
  17. End If
  18. End If
  19. Next s
  20. Next sl
  21.  
  22. End Sub
  23.  
  24. Sub GetChartData2() ' includes code to paste into excel
  25. Dim s As Shape 'gr As Graph.Chart
  26. Dim gr As Object
  27. Dim sl As Slide
  28. 'Copies data from datasheet in powerpoint
  29. For Each sl In ActivePresentation.Slides
  30. For Each s In sl.Shapes
  31. If s.Type = msoEmbeddedOLEObject Then
  32. 'we have found an OLE object
  33. 'check if it's a graph
  34. If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
  35. 'this might vary depending on what version you're using
  36. 'now get a handle on the graph object itself
  37. Set gr = s.OLEFormat.Object
  38. gr.Application.DataSheet.Cells.Copy
  39.  
  40. 'Paste into excel - this section not working
  41.  
  42. Workbooks("test.xls").Sheets("sheet1").Activate
  43. Range("B1").Select
  44. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  45. :=False, Transpose:=False
  46.  
  47. End If
  48. End If
  49. Next s
  50. Next sl
  51.  
  52. End Sub
  53.  
Dec 4 '08 #1
2 6078
Dököll
2,364 Expert 2GB
Greetings, LucasLondon!

Looks like you putin a lot of work into this, great job...

I will send over to our friends in VBA to see what can be salvaged.

Please come back to us if VB 6 is what you needed.

Good luck with the project!

Dököll
Dec 6 '08 #2
Hello Dokoll,

Just to let you know, I'm trying to do this in VBA from within excel or powerpoint and not in a standalone VB application, although of course I understand that VBA and VB are the same language!

Thanks for your effort. Looking forward to seeing some kind of soultion.

Lucas
Dec 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by runner7 | last post: by
3 posts views Thread by =?Utf-8?B?YzY3NjIyOA==?= | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.