VBA to Copy Tables in PowerPoint and Paste in Excel
I have PowerPoint file with multiple tables, and I am trying to write an excel vba code to open the file, loop through the slides and shapes, and then transfer the tables into excel. For this purpose I just want to know how to loop through a slide and transfer any and all shapes/tables to excel.
Here is my current code:
Sub PP_Test()
Dim s As Slide
Dim sh As Shape
Dim wbk As Workbook
Dim wsh As Worksheet
Set objPPT = CreateObject("Powerpoint.application")
objPPT.Visible = True
Dim file As String
file = "C:\Example.pptx"
Set pptApp = CreateObject("PowerPoint.Application")
Set pptPres = pptApp.Presentations.Open(file)
Set wbk = Workbooks("Test.xlsm")
'Loop through the slides and loop through the shapes to find all the Tables. Copy the table, and paste them in Excel'
For Each s In ActivePresentation.Slides
For Each sh In s.Shapes
'Create a new sheet in Excel'
Set wsh = wbk.Worksheets.Add(After:=wbk.Worksheets(wbk.Worksheets.Count))
' Copy/paste the shape/table'
sh.Copy
wsh.Paste
Next sh
Next s
End Sub
I currently get the following Run-Time Error on the line "For Each s In Active Presentation.Slides":
Run-time error '429': ActiveX component can't create object
I have looked around for examples, but I can only find examples of how to transfer tables from excel to PowerPoint, but not the other way around.