Using Excel VBA to change PowerPoint Slide Size - Late Binding

Question

I'm new-ish to VBA and brand new to StackOverflow, so please forgive any breaches of etiquette. I have a project to create a tool using Excel VBA that will allow the user to identify a source Excel file and then copy the print area of every worksheet in the Excel file into a slide in a newly created PowerPoint presentation. I have to use late binding because I can't assume the users of the tool will all be using the same versions of the Microsoft Office products, or that they will have enabled the reference to the PowerPoint Object Library. I have managed to do all of this. Where I am getting hung up is when I try to set the size of the slides - I have been asked for the slides to be in the 4:3 slide size available in PowerPoint 2013 (or a similar ratio if needed to make the tool work in older versions of PowerPoint - I have been trying for ppSlideSizeLetterPaper). This is throwing "Run-time error '438': Object doesn't support this property or method". If anyone could offer suggestions as to how to get around this, i'd be grateful.

Here's the relevant snippets of the code:

Public PowerPointApp As Object
Public TargetFile As Object
…
Sub Master()
Set PowerPointApp = CreateObject("PowerPoint.Application")
Err.Clear
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
If Err.Number = 429 Then
    MsgBox "PowerPoint could not be found.  Exiting macro."
    Exit Sub
End If
Set TargetFile = PowerPointApp.Presentations.Add
    PowerPointApp.Visible = True
    PowerPointApp.Activate
…<code to add slides and paste data from Excel into the slides>
PowerPointApp.TargetFile.PageSetup.SlideSize = 2    ‘this is where the error is thrown.  2 is the numeric equivalent of ppSlideSizeLetterPaper

Trigger · Accepted Answer · 2015-04-23 21:31:07Z

3

Targetfile is undefined in that context - there is no method or property called that on the application object.

As you've already have an object of that name, just use that.

TargetFile.PageSetup.SlideSize = 2

answered Apr 23, 2015 at 21:31

Trigger

6913 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Steve Rindsberg Over a year ago

's correct. The application object doesn't have a TargetFile property, which is why you're getting the error. In addition, I'd move the line that sets the slide size immediately after you've added a presentation and BEFORE you've added any content to slides. Otherwise, the content's liable to get distorted when you resize.

Scott Dyck Over a year ago

Thanks to both Trigger and Steve. I tried Trigger's suggestion (and relocated the offending line of code per Steve's suggestion), and it worked perfectly. Trigger, I would have tried this in testing, but I believed (obviously incorrectly) that I had to refer to the PowerPointApp object because I was using late binding. I clearly dont understand exactly how late binding works - gonna have to do some on this reading.

Trigger Over a year ago

Early/late binding refers to the binary interface. Early the program knows to jump to the address at #5 in the vtable (for the 5th method). In late binding it's a conversation. Hello object, do you have a command called print, the object replies yes, number 5. Your program then says please do number 5 command. Object says sure. So early binding is a function call with one level of indirection and quick. Everything is worked out at compile time. Late binding is slow, you communicate with objects in semi-english, you don't need to know what the object is.

Trigger Over a year ago

If both excel files and word files support print then creating object from them in late binding and asking them to print will work. If a new company releases a word processor after your program is released, and it supportsprint, then your program will work with it in late binding (but not early).

Trigger Over a year ago

Also it is usually best to connect to documents not applications. If you need the app object you can use something like TargetFile.Application.caption where target file is a presentation object.

Collectives™ on Stack Overflow

Using Excel VBA to change PowerPoint Slide Size - Late Binding

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related