VB error in Office 365/Excel 2016 that does not occur in Excel 2010

Hi!  We are testing our existing files before upgrading from Office 2010 to Office 365 (Office 2016).

We have a complicated Excel frontend created by users to process data and automatically create charts and graphs and ultimately export everything directly to PowerPoint.

We have done a ton of testing but have only come across one problem that I can't seem to resolve.

VB code that copies and paste a chart from Excel to PowerPoint works in Office 2010 but not in 2016.  
Here's how the code currently exists in Office 2010.
        Set activeSlide = pp.ActivePresentation.Slides(1)
        ActiveSheet.Range("BX146:BY158").Select
        ActiveSheet.Range("BX146:BY158").Copy
        activeSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject).Select

Open in new window

This gives us the following error in 2016.
Run-time error '424':
Object required
(also see screenshot in Word doc)

I read posts online and made the following code changes but ended up with the same error.
   
        Set activeSlide = pp.ActivePresentation.Slides(1)
        ActiveSheet.Range("BX146:BY158").Select
        ActiveSheet.Range("BX146:BY158").Copy
       '9/5/18 activeSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject).Select 'Runs table
        '9/5/18
        Dim pptshape As PowerPoint.Shape
        Set pptshape = activeSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject)
        pptshape.Select
        '9/5/18 end

Open in new window


As I said, this code runs fine in Excel 2010.

Does anyone have any ideas on how to resolve this issue?  Also, please note that we need to be able to paste the object (which is a table in Excel) so that PowerPoint links to the table in Excel and "sees" any updates that may be made in Excel to the table.

Thank you for your help.

Alexis
isummary-powerpoint-creation-error-.docx
alexisbrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Can you try separating the Paste and Select methods? Copying to/from the clipboard is a very tricky business, especially when the data is complex and VBA can often "run ahead" before the clipboard is ready. Example:

activeSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject
activeSlide.Shapes(activeSlide.Shapes.Count).Select msoTrue

Open in new window


If this doesn't work, you could try adding a delay between the Copy and Paste methods and also a DoEvents call.
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Oops - I didn't scroll down to see the other thing you tried. Which line is raising the error? Can you manually copy the range BX146:BY158 to the clipboard via the Excel UI and then try this paste command in the Immediate pane of the PowerPoint VBE?

ActivePresentation.Slides(1).Shapes.PasteSpecial ppPasteOLEObject

Open in new window

0
alexisbrAuthor Commented:
Thank you.  I tried to test as you said but I could not figure out how to get the immediate window in Powerpoint to know I had something in the clipboard to paste.  When I execute the code in the immediate window, the Macros window appears as if it wants me to select a macro to run.

The line raising the error in the old code is:
activeSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject).Select

And in the new code is:
Set pptshape = activeSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject)

You can also see it in the file that I attached to my original question.  The line that's bombing is highlighted in yellow.

I just tested again.  I tested a file in Excel 2010 and the PPT file was created perfectly. Then I opened the same file in Excel 2016 but got the error when the table is copied from Excel into the PPT file.

Alexis
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jamie GarrochPowerPoint Consultant & DeveloperCommented:
OK. In you original Word doc, the failing line is a concatenation of two methods so you can't tell if it's the PasteSpecial method that's raising the error e.g. activeSlide.Shapes reference issue or the Select method is failing e.g. no object to select.

The reason for suggesting the manual approach by using the Immediate window in the PowerPoint VBE was to eliminate a potential timing issue but if the code fails as you step through it then this isn't the problem (I assume you have tried a breakpoint on the failing line and used F8 to step through the code one line at a time).

Are you saying the this single method line raises the error?

Set pptshape = activeSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject)

Open in new window


Another idea. When you use the PasteSpecial method it will return an object of type ShapeRange, not Shape as you have assigned to the variable pptshape but you can index the single pasted shape in the Shape Range (of 1) as follows:

Set pptshape = activeSlide.Shapes.PasteSpecial(ppPasteOLEObject)(1)

Open in new window


It might also be an issue with your use of Early Binding. Try removing the reference to the PowerPoint library and declaring all PowerPoint objects as type "Object". You'll then have to add any PP-specific constants e.g.

Const ppPasteOLEObject = 10

Open in new window


One last thing, step through the code and when you get to the failiong line in question, type the following in the Immediate pane of the Excel VBE to see if the reference to the Shapes collection is set correctly:

?TypeName(activeSlide.Shapes)

Open in new window

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I ran these lines from Excel 2016 without any issue...
Set activeSlide = pp.ActivePresentation.Slides(1)
Range("BX146:BY158").Copy
activeSlide.Shapes.PasteSpecial ppPasteOLEObject

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alexisbrAuthor Commented:
Thank you, Subodh Tiwari (Neeraj).  I tried the tweak you did in line 3 and that has appeared to help a lot but I am still testing and making the same change to many more lines of code in the program.  I will give more feedback soon.

Alexis
0
alexisbrAuthor Commented:
Thank you both very much.  In the end, by using code suggested by Subodh Tiwari (Neeraj), which was separating the copy, paste and select lines, fixed the problem but I also used the idea to pause in between some pastes from Jamie Garroch.

I appreciate your time and expertise for a tough problem for which I was not finding answers on the web.

Alexis
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Alexis! Glad we could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.