Copy table in powerpoint to excel tabs and rename tabs

hwong8848

New Member
Joined
Oct 9, 2022
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hi All!

I have a question regarding looping through all tables and copy them to excel.

VBA Code:
Sub PP_Test()
    Const filename = "C:\Users\file.pptm"

    Dim wbk As Workbook, wsh As Worksheet
    
    Dim pptApp As PowerPoint.Application, pptPres As PowerPoint.Presentation
    Set pptApp = New PowerPoint.Application
    pptApp.Visible = True
        
    Set pptPres = pptApp.Presentations.Open(filename)
    
    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
    Dim s As PowerPoint.Slide, sh As PowerPoint.Shape
    For Each s In pptPres.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

However, user defined function error keep pooping up. Is there any quick fix?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
It looks fine. Worked when I ran it. I do see that your file names are pptm and xlsm - both macro enabled. You likely have a custom function in one of them that is causing the error. For example, your workbook may have a NewSheet, SheetActivate or SheetDeactive event-driven macro that is not gettig what it needs and throwing an error. Just a guess.

One quick tip - between Next s and End Sub, add lines to clean up. It will keep stray stuff ffom causing problems.
VBA Code:
    pptPres.Close: Set pptPres = Nothing
    pptApp.Quit: Set pptApp = Nothing
 
Upvote 0
Ah - didn't manage to get it fixed.
I manually changed the file type to be both macro enabled as I thought it has to be before I run this.

The problem still persist though, user-defined function error..
 
Upvote 0
It's hard to say I the code looks OK and I can't replicate the error. Two things to look for - is your Excel application opening a Personal.xlsm file in the background. Go inot VBA and look at the list of open files. The user function may be in there. Secondly, create a whole new PPTX with a table or 2 and a whole new XLSX file to paste to and then a whole new XLSM file to run the code. See if you can get it to work if there is a fresh start ... just try to narrow down where the error is coming from.

And this might not be it but "C:\Users\file.pptm" is't typically a valid file path. It's typically "C:\Users\[username]\[foldername]\file.pptm"
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top