This article shows how to batch convert Microsoft PowerPoint presentations into PDF file format using Excel VBA program. This is one of the common problem faced by professionals when clients expect files in PDF format and we have the files in PPTX format. It's easy to convert a single file in PDF but cumbersome to convert multiple files manually. In Microsoft Excel we can automate tasks via macro, that's what we will be discussing it in this post.
The MS Office Add-in "Save as PDF or XPS" is pre-installed in the latest versions of MS Office (Office 2010+ package). If this add-in is not installed on your computer, download it from the Microsoft site. Google search on "Save as PDF or XPS MS Office" and you'll find the link working1. Convert the whole powerpoint file into PDF
The following program creates a PDF file of the whole powerpoint presentation from files in a folder.The PDF files are saved in the same directory where PPT files are stored.Enter the folder path in cell C3 as shown below :
Option Explicit Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Sub Converter() Dim cnt As Integer, currfile As String Dim TrimFile As String, Path As String, FilesInPath As String _ , MyFiles() As String, Fnum As Long Dim CalcMode As Long, LPosition As Long Dim StartTime As Date, EndTime As Date ThisWorkbook.Activate currfile = ActiveWorkbook.Name Windows(currfile).Activate Sheets("Sheet1").Activate StartTime = Timer Path = Range("C3").Text & "\" FilesInPath = Dir(Path & "*.pp*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If Fnum = 0 Do While FilesInPath <> "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With If Fnum > 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue On Error Resume Next Set oPPTFile = oPPTApp.Presentations.Open(Path & MyFiles(Fnum)) On Error GoTo 0 If Not oPPTFile Is Nothing Then LPosition = InStr(1, oPPTFile.Name, ".") - 1 TrimFile = Left(oPPTFile.Name, LPosition) On Error Resume Next oPPTFile.ExportAsFixedFormat oPPTFile.Path & "\" & TrimFile & ".pdf", _ ppFixedFormatTypePDF, ppFixedFormatIntentPrint End If oPPTFile.Close Next Fnum End If With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With oPPTApp.Quit Set oPPTFile = Nothing Set oPPTApp = Nothing EndTime = Timer MsgBox " Task succesfully completed in " & Format(EndTime - StartTime, "0.00") & " seconds" End Sub
How to use
Download the workbook and enter the folder path in cell C3.
And then hit PDF Conversion button.
OR
- Open Excel. Press Alt + F11 to open the visual basic editor.
- Go to Tools >> References and check Microsoft Powerpoint Object library
- Go to Insert >> Module to insert a new module.
- Paste the above program into the module.
- Mention the folder path in cell C3.
- Press Alt + F8, select "Converter" macro and hit Run button.
2. Convert the specified powerpoint slide into PDF
The following program creates a PDF file of the specified powerpoint slide from presentations in a folder. It displays a prompt in a dialog box to enter the slide number.Option Explicit Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Dim sld As Slide Sub Converter() Dim currfile As String, TrimFile As String _ , Path As String, FilesInPath As String _ , MyFiles() As String, Fnum As Long Dim CalcMode As Long, LPosition As Long Dim StartTime As Date, EndTime As Date Dim SlidePos As Variant ThisWorkbook.Activate currfile = ActiveWorkbook.Name Windows(currfile).Activate Sheets("Sheet1").Activate StartTime = Timer Path = Range("C3").Text & "\" SlidePos = InputBox("Enter slide number") Select Case StrPtr(SlidePos) Case 0 MsgBox "Task Aborted" Exit Sub End Select FilesInPath = Dir(Path & "*.pp*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If Fnum = 0 Do While FilesInPath <> "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With If Fnum > 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue On Error Resume Next Set oPPTFile = oPPTApp.Presentations.Open(Path & MyFiles(Fnum)) On Error GoTo 0 If Not oPPTFile Is Nothing Then LPosition = InStr(1, oPPTFile.Name, ".") - 1 TrimFile = Left(oPPTFile.Name, LPosition) On Error Resume Next oPPTFile.Slides.Range(SlidePos).Select oPPTFile.ExportAsFixedFormat oPPTFile.Path & "\" & TrimFile & ".pdf", ppFixedFormatTypePDF, RangeType:=ppPrintSelection End If oPPTFile.Close Next Fnum End If With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With oPPTApp.Quit Set oPPTFile = Nothing Set oPPTApp = Nothing EndTime = Timer MsgBox " Task succesfully completed in " & Format(EndTime - StartTime, "0.00") & " seconds" End Sub
Download the workbook and enter the folder path in cell C3.
And then hit PDF Conversion button. After that it will prompt you to enter a slide number.
3. Convert each slide as a seperate PDF file
The following program creates a PDF file of each slide from the presentations in a folder.Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Dim oPPTSlide As PowerPoint.Slide Sub Converter() Dim currfile As String, TrimFile As String _ , Path As String, FilesInPath As String _ , MyFiles() As String, Fnum As Long Dim i, CalcMode As Long, LPosition As Long Dim StartTime As Date, EndTime As Date ThisWorkbook.Activate currfile = ActiveWorkbook.Name Windows(currfile).Activate Sheets("Sheet1").Activate StartTime = Timer Path = Range("C3").Text & "\" FilesInPath = Dir(Path & "*.pp*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If Fnum = 0 Do While FilesInPath <> "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With If Fnum > 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue On Error Resume Next Set oPPTFile = oPPTApp.Presentations.Open(Path & MyFiles(Fnum)) On Error GoTo 0 If Not oPPTFile Is Nothing Then LPosition = InStr(1, oPPTFile.Name, ".") - 1 TrimFile = Left(oPPTFile.Name, LPosition) On Error Resume Next For Each oPPTSlide In oPPTFile.Slides OutputPath = TrimFile & "-" & oPPTSlide.Name oPPTSlide.Select oPPTFile.ExportAsFixedFormat oPPTFile.Path & "\" & OutputPath & ".pdf", _ ppFixedFormatTypePDF, RangeType:=ppPrintSelection Next oPPTSlide oPPTFile.Close End If Next Fnum End If With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With oPPTApp.Quit Set oPPTFile = Nothing Set oPPTApp = Nothing EndTime = Timer MsgBox " Task succesfully completed in " & Format(EndTime - StartTime, "0.00") & " seconds" End Sub
Download the workbook and enter the folder path in cell C3.
And then hit PDF Conversion button.
Great article. thanks it was very helpful.
ReplyDeleteI really like the third strategy, but for some reason, the macro is only extracting and saving slides as PDF's for files that were modified with today's date. I've tested this multiple times - the macro opens all decks in my file path, but only converts the files that were modified today. Any suggestions on how to fix this?
ReplyDeleteThanks a ton..
ReplyDeleteuseful article, thank you!
ReplyDeleteSame as first strategy any possibilities to export it as excel instead of pdf
ReplyDeleteAfter I use the code, the system prompts an error stating: 'Compile Error: User-defined type not defined'.
ReplyDeleteOption 3 is good one. Run the program also. Got successfully done. But unable to find PDF files. Please guide.
ReplyDeleteComo fazer para exportar em xls? por gentileza
ReplyDeleteplease how to convert to XML or XLS?
ReplyDeleteAppreciated thanks
ReplyDelete