By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,069 Members | 1,716 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,069 IT Pros & Developers. It's quick & easy.

Extracting data from Powerpoint datasheet/MSgraph to Excel

P: 16
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
Share this Question
Share on Google+
2 Replies


Dököll
Expert 100+
P: 2,364
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

P: 16
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.