VBA to Copy Tables in PowerPoint and Paste in Excel

cbokane 1 Reputation point
2021-02-09T15:49:46.16+00:00

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.

0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.