472,119 Members | 1,826 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

Extracting data from Powerpoint datasheet/MSgraph to Excel


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.


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
  17. End If
  18. End If
  19. Next s
  20. Next sl
  22. End Sub
  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
  40. 'Paste into excel - this section not working
  42. Workbooks("test.xls").Sheets("sheet1").Activate
  43. Range("B1").Select
  44. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  45. :=False, Transpose:=False
  47. End If
  48. End If
  49. Next s
  50. Next sl
  52. End Sub
Dec 4 '08 #1
2 6227
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!

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.

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 leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.