Powerpoint to PDF from Excel VBA

Posted on 2011-10-29
Last Modified: 2012-05-12

I have a powerpoint file that is being populated by Excel using VBA code.

At the end of the routine, I need to save the powerpoint file to PDF using the inbuilt PDF print in MS Office.

I have enclosed the code I am using to populate the ppt.

Can anyone help me with getting the routine to save the ppt as pdf with a filesaveas prompt?

Private Sub CmdQuote_Click()
Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim oPPTFile As PowerPoint.Presentation
Dim SlideNum As Integer
Dim FileName As String

Dim strPresPath As String, strExcelFilePath As String, strNewPresPath As String
    strPresPath = "C:\Users\SHAUN\Documents\PROTEGE\Sales Pack Case Study - 1000.pptm"
    strNewPresPath = "C:\Users\SHAUN\Documents\PROTEGE\"".pptx"
    Dim FileFormatstr As String
    Dim Fname As Variant
    Dim Myvar As Object, FixedFilePathName As String, OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean
    Set oPPTApp = CreateObject("PowerPoint.Application")
    oPPTApp.Visible = msoTrue
    Set oPPTFile = oPPTApp.Presentations.Open(strPresPath)
    SlideNum = 4
    Set oPPTShape = oPPTFile.Slides(SlideNum).Shapes("Outs")
    'ROW 1 "LAMP_LIFE"
    oPPTShape.Table.Cell(1, 2).Shape.TextFrame.TextRange.Text = Cells(12, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(1, 3).Shape.TextFrame.TextRange.Text = Cells(12, 8).Text
    oPPTShape.Table.Cell(1, 4).Shape.TextFrame.TextRange.Text = Cells(12, 12).Text
    oPPTShape.Table.Cell(2, 2).Shape.TextFrame.TextRange.Text = Cells(13, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(2, 3).Shape.TextFrame.TextRange.Text = Cells(13, 8).Text
    oPPTShape.Table.Cell(2, 4).Shape.TextFrame.TextRange.Text = Cells(13, 12).Text
    'ROW 3 ""
    oPPTShape.Table.Cell(3, 2).Shape.TextFrame.TextRange.Text = Cells(14, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(3, 3).Shape.TextFrame.TextRange.Text = Cells(14, 8).Text
    oPPTShape.Table.Cell(3, 4).Shape.TextFrame.TextRange.Text = Cells(14, 12).Text
    'ROW 4 ""
    oPPTShape.Table.Cell(4, 2).Shape.TextFrame.TextRange.Text = Cells(15, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(4, 3).Shape.TextFrame.TextRange.Text = Cells(15, 8).Text
    oPPTShape.Table.Cell(4, 4).Shape.TextFrame.TextRange.Text = Cells(15, 12).Text
    'ROW 5 ""
    oPPTShape.Table.Cell(5, 2).Shape.TextFrame.TextRange.Text = Cells(16, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(5, 3).Shape.TextFrame.TextRange.Text = Cells(16, 8).Text
    oPPTShape.Table.Cell(5, 4).Shape.TextFrame.TextRange.Text = Cells(16, 12).Text
    'ROW 6 ""
    oPPTShape.Table.Cell(6, 2).Shape.TextFrame.TextRange.Text = Cells(17, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(6, 3).Shape.TextFrame.TextRange.Text = Cells(17, 8).Text
    oPPTShape.Table.Cell(6, 4).Shape.TextFrame.TextRange.Text = Cells(17, 12).Text
    'ROW 7 ""
    oPPTShape.Table.Cell(7, 2).Shape.TextFrame.TextRange.Text = Cells(19, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(7, 3).Shape.TextFrame.TextRange.Text = Cells(19, 8).Text
    oPPTShape.Table.Cell(7, 4).Shape.TextFrame.TextRange.Text = Cells(19, 12).Text
    'ROW 8 ""
    oPPTShape.Table.Cell(8, 2).Shape.TextFrame.TextRange.Text = Cells(20, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(8, 3).Shape.TextFrame.TextRange.Text = Cells(20, 8).Text
    oPPTShape.Table.Cell(8, 4).Shape.TextFrame.TextRange.Text = Cells(20, 12).Text
    'ROW 9 ""
    oPPTShape.Table.Cell(9, 2).Shape.TextFrame.TextRange.Text = Cells(22, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(9, 3).Shape.TextFrame.TextRange.Text = Cells(22, 8).Text
    oPPTShape.Table.Cell(9, 4).Shape.TextFrame.TextRange.Text = Cells(22, 12).Text
    'ROW 10 ""
    oPPTShape.Table.Cell(10, 2).Shape.TextFrame.TextRange.Text = Cells(21, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(10, 3).Shape.TextFrame.TextRange.Text = Cells(21, 8).Text
    oPPTShape.Table.Cell(10, 4).Shape.TextFrame.TextRange.Text = Cells(21, 12).Text
    'ROW 11 ""
    oPPTShape.Table.Cell(11, 2).Shape.TextFrame.TextRange.Text = Cells(22, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(11, 3).Shape.TextFrame.TextRange.Text = Cells(22, 8).Text
    oPPTShape.Table.Cell(11, 4).Shape.TextFrame.TextRange.Text = Cells(22, 12).Text
    'ROW 12 ""
    oPPTShape.Table.Cell(12, 2).Shape.TextFrame.TextRange.Text = Cells(23, 4).Text 'x = Row, y = Column
    oPPTShape.Table.Cell(12, 3).Shape.TextFrame.TextRange.Text = Cells(23, 8).Text
    oPPTShape.Table.Cell(12, 4).Shape.TextFrame.TextRange.Text = Cells(23, 12).Text
    Set oPPTShape = Nothing
    Set oPPTShape = oPPTFile.Slides(SlideNum).Shapes("TxtBlack")
    oPPTShape.TextFrame.TextRange = Format(ActiveSheet.Range("D30").Text, "#,##")
    'oPPTFile.SaveAs strNewPresPath

    'CLEAN UP    
    Set oPPTShape = Nothing
    Set oPPTFile = Nothing
    Set oPPTApp = Nothing
    MsgBox "Presentation Created", vbOKOnly + vbInformation
End Sub

Open in new window

Question by:creativefusion
    LVL 41

    Accepted Solution

    Sure, add these lines around line 93:

        pdfName = ActiveWorkbook.Path & "\test.pdf"
        oPPTFile.SaveAs Filename:=pdfName, FileFormat:=ppSaveAsPDF

    LVL 41

    Expert Comment

    This assumes you have Office 2010.  You're showing Excel 2007, but you mention the "in built PDF Print".  Otherwise, you'll need Acrobat Distiller (great little tip here, with that:, or you'll need to download an add-on to do this.

    Let me know exactly your configuration, or if my post, above, works for you.


    LVL 41

    Expert Comment

    Sorry for the multiple messages.  I should have posted this in my last post.

    If you need to download the 2007 add-on, see this link:

    The SaveAsPDF should work for you with this add-on.  Let me know if it doesn't and I'll back down to Office 2007 and make it work for you.



    Author Closing Comment

    Hi Dave,

    We meet again!!

    It works perfectly with my office 2007 software. Thanks very much.

    LVL 41

    Expert Comment

    Well - that's excellent.  I was just starting up my VM Machine with Office 2007 to check it out.

    Very cool - you didn't have to load the add-on?  or did you already have it?


    Author Comment

    Already had it loaded.

    I have a peice of code to check that it is loaded as well.

    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
    & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now