Save powerpoint presentation as as a pdf in vba

Question

I have looked at a lot of answers to this question but can't figure out what I have done wrong. I am trying to create a pdf file. I get my data from an excel file and copy it into powerpoint. I then try to save as pdf but it keeps giving me an error (object required) at the saving pdf section of the macro (see below). I tried changing it multiple times but still can't get it to work. Have attached code below. After I fix this problem, I need to be able to change the size of the object I pasted in - how do I do that.

Sub CreatePDFfiles_4()


Dim PPapp As Object
Dim PPPres As Object
Dim first_file As Boolean
Dim investorname As String
Dim path As String


Sheets("printing").Select
Range("g2").Select
file1 = ActiveCell.Value
Range("g3").Select
path = ActiveCell.Value
Range("g8").Select
investorname = ActiveCell.Value
Range("i8").Select
cor_file_name = ActiveCell.Value
DestinationPPT = "C:\Users\name\Documents\company\Investment Model\printing macro\template.pptx"


While investorname <> "end"
    ActiveCell.Offset(0, -1).Select
    print_data = ActiveCell.Value
    If print_data = "Yes" Then

        ' Initialize PowerPoint Object Library
        Set PPapp = CreateObject("Powerpoint.Application")
        PPapp.Visible = True

        ' Open presentation
        Set PPPres = PPapp.Presentations.Open(DestinationPPT)

        'Copy excel file data
        Windows(file1).Activate
        Sheets(investorname).Select
        Range("b1:r46").Select
        Selection.Copy

        'Paste into existing powerpoint template slide that is open
        PPPres.slides(1).Shapes.Paste

        'Save as pdf
        PPPres.ExportAsFixedFormat ActivePresentation.path & "\" & cor_file_name & ".pdf", ppFixedFormatTypePDF, ppFixedFormatIntentPrint



        PPapp.Quit
        Set PPapp = Nothing

Unless you have a reference to the Powerpoint library, you will need to give values to all the constants such as ppFixedFormatTypePDF. Put Option Explicit as the first line of your code module and it will complain about anything that isn't defined. — YowE3K
Re "After I fix this problem ..." - After the current issue is fixed, raise another question. Trying to solve multiple issues in one questions just ends up getting the question closed as "too broad". — YowE3K
I have not coded much . I tried putting it in after Sub CreatePDFfiles_4() and it woudn't take it. Where should I put Option Explicit? — user3194519
Option Explicit should be the first line of your code module - i.e. before any other line of code. Go to the very top of the code (possibly your Sub statement, possibly something else), insert a line before it, enter Option Explicit. — YowE3K
Other than those PPT constants, you should also have issue with ActivePresentation inside Excel VBA. Where does cor_file_name come from? You don't seem to have pasted all the code for that Sub? — PatricK

LCCJR LCCJR · Accepted Answer · 2020-03-28T12:18:10

None of that worked for me. it was as simple as:

file_name = (path and name of the file you want to open)
Path = (where you want to save it)
PdfFileNm = (name of the file)

Set PPT = CreateObject("PowerPoint.Application")
Set Pres = PPT.presentations.Open(file_name)
PPT.ActivePresentation.SaveAs Path & PdfFileNm & ".pdf", 32

Save powerpoint presentation as as a pdf in vba

1 Answers