Convert Multiple Powerpoint Files into PDF with Excel VBA

Deepanshu Bhalla 10 Comments , ,

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 working

1. 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 :
PPT to PDF Conversion
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

  1. Open Excel. Press Alt + F11 to open the visual basic editor.
  2. Go to Tools >> References and check Microsoft Powerpoint Object library
  3. Go to Insert >> Module to insert a new module.
  4. Paste the above program into the module.
  5. Mention the folder path in cell C3.
  6. 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.
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 10 Responses to "Convert Multiple Powerpoint Files into PDF with Excel VBA"
  1. Great article. thanks it was very helpful.

    ReplyDelete
  2. I 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?

    ReplyDelete
  3. useful article, thank you!

    ReplyDelete
  4. Same as first strategy any possibilities to export it as excel instead of pdf

    ReplyDelete
  5. After I use the code, the system prompts an error stating: 'Compile Error: User-defined type not defined'.

    ReplyDelete
  6. Option 3 is good one. Run the program also. Got successfully done. But unable to find PDF files. Please guide.

    ReplyDelete
  7. Como fazer para exportar em xls? por gentileza

    ReplyDelete
  8. please how to convert to XML or XLS?

    ReplyDelete
Next → ← Prev