Powerpoint to PDF from Excel VBA

Posted on 2011-10-29
Medium Priority
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
  • 4
  • 2
LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 37052038
Sure, add these lines around line 93:

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

LVL 42

Expert Comment

ID: 37052048
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:  http://www.officekb.com/Uwe/Forum.aspx/powerpoint/57737/vba-print-ppt-file-to-pdf, 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 42

Expert Comment

ID: 37052053
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:  http://labnol.blogspot.com/2006/09/office-2007-save-as-pdf-download.html

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.


Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Closing Comment

ID: 37052103
Hi Dave,

We meet again!!

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

LVL 42

Expert Comment

ID: 37052111
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

ID: 37052373
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


Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

621 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