Excel/PowerPoint VBA and late binding

Question

I've been building a tool to generate PowerPoint slides from data in an excel workbook. I move back and forth between a computer at work and my computer at home. The work computer has Excel 2013 while the home computer has 2016. This generally isn't an issue...when I move from the home computer to the work computer I just have to change the reference from the v16 object library to the v15 object library.

Earlier this week though I ran into an error I couldn't resolve...detailed here One of the suggestions to resolve it was to switch to late binding so I didn't need the reference. That was a pretty easy switch, but it has led to an error in the resulting ppt slide...

In the original (early binding) version I set things up like

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PPLayout As CustomLayout
Dim PPshape As Variant
Dim tBox As PowerPoint.Shape

And then

Set PPApp = New PowerPoint.Application
Set PPPres = PPApp.Presentations.Open(fileName, msoTrue, , msoFalse)

And then part of the slide was built with

wsGenerator.Activate
wsGenerator.Range("B32:L37").Select
Selection.Copy
With PPSlide.Shapes.PasteSpecial(ppPasteMetafilePicture)
     .Top = 450
     .Left = 50
     .Height = 100
     .Width = 325
End With

Part of the resulting slide looks like this

The table at the bottom is the part pasted by the above code.

When I change to late binding, I simply make these changes

'Dim PPApp As PowerPoint.Application
Dim PPApp As Object

'Dim PPPres As PowerPoint.Presentation
Dim PPPres As Object

'Dim PPSlide As PowerPoint.Slide
Dim PPSlide As Object

'Dim PPLayout As CustomLayout
Dim PPLayout As Object

'Dim tBox As PowerPoint.Shape
Dim tBox As Object

Dim PPshape As Variant

And then

'Set PPApp = New PowerPoint.Application
Set PPApp = CreateObject("PowerPoint.Application")

Everything else remains the same. The resulting chart now looks like this

Note that it now extends off the bottom of the slide.

Any ideas as to what that's about?

Why not try www.pptxbuilder.com, it does something similar to what you are doing... — Boosted_d16

ThunderFrame ThunderFrame · Accepted Answer · 2017-11-22T04:40:40

It seems that you've changed your declarations to late-binding, but you may still be using some of the PowerPoint constants (such as ppPasteMetafilePicture), which will no longer resolve to their early-bound values, and instead default to 0.

You'll need to define a local constant for ppPasteMetafilePicture in order for the value to be available.

As an aside, you should always use Option Explicit and then the VBE will automatically spot usages of undeclared constants.

Excel/PowerPoint VBA and late binding

1 Answers