Advent Day 24 Ms Access Gurus
Home Code

VBA > Word automation > Save as PDF

Convert anything Word can open to a PDF file! Use Word to open a file and then save it as PDF using VBA.

Run in any Office application such as Access, Excel, PowerPoint, Visio, Project, ... or from Word on the active document using the special version starting with "RunFromWord".

Screen shots

This is a web page created by Access with my Advent calendar posts this month (December 2018) with code categories and titles, and what I plan to post for Christmas too. Now it is also a PDF file.

Calendar Report

To make a calendar report from a query in Access yourself, download this free tool, and use the required aliases for calculated field names.

Next screen shot is a Word document showing the SQL for all the queries in a database — it was a Word document ... now it it also a PDF.

Examples from Access


Open sPathFile as an HML document (piOpenFormat=wdOpenFormatWebPages=7) in Word. Orientation is Landscape (piOrientation=wdOrientLandscape=1). Name will be automatically assigned. Export to PDF and open the PDF file when done.

Application.FollowHyperlink Word_SavePDF(sPathFile,7,1)

Convert a Word document to a PDF. Open format is auto (default), orientation is portrait (default). Assign name automatically (default). Don't show message when done (false). Open the PDF file.

Application.FollowHyperlink Word_SavePDF("c:\path\Mydocument.docx", , , , False)


Examples from Word

If you are running in Word and want to use the ActiveDocument, then run the procedure designed especially for Word, RunFromWord_ActiveDocument_ExportPDF

Call RunFromWord_ActiveDocument_ExportPDF(ActiveDocument)

Export active document (ActiveDocument) to PDF, but don't open or show message when done. Open path

Call RunFromWord_ActiveDocument_ExportPDF(ActiveDocument, False)
ActiveDocument.FollowHyperlink ActiveDocument.Path

Run and Open from Excel

Run Word_SavePDF to return a path and filename. In Excel, you can use FollowHyperlink from a Workbook (or ActiveWorkbook) object.

Run and Open from PowerPoint

Run Word_SavePDF to return a path and filename. In PowerPoint, you can use FollowHyperlink from a Presentation (or ActivePresentation) object.

Logic: Word_SavePDF

There is no error handler.

Set a constant to convert inches to points. There are 72 points in an inch.

Construct a path\filename for the PDF unless it was passed. If the file already exists, then delete it.

Call WordOpen to open Word.

Open the document. Be sure to send the WdOpenFormat if the document is a web page or something else Word can only open if the format is specified.

Set orientation to portrait (unless landscape was specified by piOrientation), Set to narrow margins (1/2 inch all around). Word expects points for the value.

Export the document to PDF format using ExportAsFixedFormat.

Call WordClose to quit and release Word.

Set the function return to the path and file name of the PDF.

Parameters

Code

' Module Name: mod_Word_SavePDF
'*************** Code Start *****************************************************
' Purpose  : Use Word to open a file and then save it as PDF
' Author   : crystal (strive4peace)
' Return   : String
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'--------------------------------------------------------------------------------

'                              Module Declaration
'--------------------------------------------------------------------------------
'flag to quit Word before releasing application object
Dim pBooQuitWord As Boolean
'--------------------------------------------------------------------------------

'                              Word_SavePDF
'--------------------------------------------------------------------------------'
Function Word_SavePDF(psPathFile As String _
   , Optional piOpenFormat As Integer = 0 _
   , Optional piOrientation As Integer = 0 _   
   , Optional psPathFilePDF As String = "" _
   , Optional pBooShowMessage As Boolean = True _
   ) As String
'
' 180815 strive4peace, 181224
   '  CALLS
   '     WordOpen
   '     WordClose
   '
   ' RETURNS
   '  path\file name of PDF file created

   Word_SavePDF = "" 'nothing made yet
   
   Const Inches2Points = 72 '72 points/inch
   
   Dim sPathFilePDF As String _
      , iPos As Integer _
      , sExtension As String
      
   Dim oWord As Object _
      , oDoc As Object  'Word.Document
      
   If psPathFilePDF <> "" Then
      'if a specific path\filename was passed, use it
      sPathFilePDF = psPathFilePDF
   Else
      'construct PDF path\filename
      'move file extension and add .pdf
      iPos = InStrRev(psPathFile, ".")
      sExtension = Mid(psPathFile, iPos + 1)
      sPathFilePDF = Left(psPathFile, iPos - 1) _
         & "__" & sExtension & ".pdf"
   End If
   
   If Dir(sPathFilePDF) <> "" Then
      Kill sPathFilePDF
      DoEvents
   End If
   
   Set oWord = WordOpen
   
   Set oDoc = oWord.Documents.Open(FileName:=psPathFile _
         , AddToRecentFiles:=False _
         , ConfirmConversions:=False _
         , Format:=piOpenFormat _
         , ReadOnly:=True _
         , Revert:=False _
         )

   With oDoc.PageSetup
      'wdOrientPortrait =0, wdOrientLandscape=1    
      .Orientation = piOrientation
      .TopMargin = 0.5 * Inches2Points
      .BottomMargin = 0.5 * Inches2Points
      .LeftMargin = 0.5 * Inches2Points
      .RightMargin = 0.5 * Inches2Points
   End With
   
   'ExportFormat is set for PDF (17)
   ' 17 = wdExportFormatPDF (ExportFormat)
   ' 0 = wdExportOptimizeForPrint (OptimizeFor)
   ' 0 = wdExportAllDocument (Range)
   ' 0 = wdExportDocumentContent (Item)
   ' 0 = wdExportCreateNoBookmarks (CreateBookmarks)
   
   oDoc.ExportAsFixedFormat _
         OutputFileName:=sPathFilePDF _
         , ExportFormat:=17 _
         , OpenAfterExport:=False _
         , OptimizeFor:=0 _
         , Range:=0 _
         , From:=1 _
         , To:=1 _
         , Item:=0 _
         , IncludeDocProps:=True _
         , KeepIRM:=True _
         , CreateBookmarks:=0 _
         , DocStructureTags:=True _
         , BitmapMissingFonts:=True _
         , UseISO19005_1:=False
                
   oDoc.Close False
   Set oDoc = Nothing
   Call WordClose(oWord)       

   Word_SavePDF = sPathFilePDF
   
   If pBooShowMessage = True Then
      MsgBox sPathFilePDF & " is generated", , "Done"
   End If
   
End Function

'--------------------------------------------------------------------------------

'                              WordOpen
'--------------------------------------------------------------------------------
Public Function WordOpen() As Object
'180816 strive4peace
'  return reference to Word Application
   On Error Resume Next
   Err.Clear
   'use Word if it is already running
   Set WordOpen = GetObject(, "Word.Application")
   If Err.Number <> 0 Then
      'open Word
      Set WordOpen = CreateObject("Word.Application")
      pBooQuitWord = True
   End If
   Err.Clear
End Function

'--------------------------------------------------------------------------------

'                              WordClose
'--------------------------------------------------------------------------------
Public Sub WordClose(poWord As Object)
'180307 strive4peace
   'quit Word and release object variable
   On Error Resume Next
   If pBooQuitWord Then
      poWord.Quit
   End If
   Set poWord = Nothing
End Sub

'--------------------------------------------------------------------------------

'                              RunFromWord_ActiveDocument_ExportPDF
'--------------------------------------------------------------------------------
Function RunFromWord_ActiveDocument_ExportPDF( _
   oDoc As Object _
   , Optional booOpenDocAfterExport As Boolean  = True _
   , Optional booOpenFolderAfterExport As Boolean  = False _
   ) As String
   
   Dim sPathFilePDF As String
      
   With oDoc
      sPathFilePDF = Left(.FullName _
         , InStrRev(.FullName, ".") - 1) _
         & "__" & Mid(.FullName, InStrRev(.FullName, ".") + 1) _
         & ".pdf"
      '17 = pdf format
      .ExportAsFixedFormat _
         OutputFileName:=sPathFilePDF _
         , ExportFormat:=17 _
         , OpenAfterExport:=booOpenDocAfterExport
      If booOpenFolderAfterExport = True Then
         .FollowHyperlink .Path
      End If
   End With
   RunFromWord_ActiveDocument_ExportPDF = sPathFilePDF
End Function
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

Back Story

On this day of Christmas eve (when I started this example), I want to show how to convert files to PDF, and also to summarize the code posted during Advent ... so a calendar made for a web page converted to a PDF seemed like a good example to start with! The biggest joy of Christmas is giving to others, not just this time of year, but all the time. We are all connected.

This year, I made a goal to post code for each day of Advent – to give back to the world that gives to me. I appreciate many of you even though I lack social skills to express that well.

Today, as I am finally posting this, it is the first day of Christmas.

My last post in this series, for Christmas itself, will be how to play sounds, since music is a big passion of mine. There are 12 days of Christmas ... the code will be posted before Christmas ends ~

I am also hoping others will also share links for the code index.

References

Docs / Office VBA Reference / Word / Object model / Enumerations / WdOpenFormat

Help: WdOpenFormat enumeration (Word)

Docs / Office VBA Reference / Word / Object model / Enumerations / WdExportFormat

Help: WdExportFormat enumeration (Word)

Docs / Office VBA Reference / Word / Object model / Document object / Methods / ExportAsFixedFormat

Help: Document.ExportAsFixedFormat method (Word)

Docs / Office VBA Reference / Word / Object model / Document object / Methods / FollowHyperlink

Help: Document.FollowHyperlink method (Word)

Share with others

here's the link to copy:

https://MsAccessGurus.com/VBA/Code/Word_SaveAsPDF.htm

Do you have something to say or share?

It is interesting to hear from you. Was something not clear? Did you find a bug? Is an explanation wrong or not sufficient? Do you want the code do more (there is always more)?

Some of you write to say thanks and tell me what you're doing with Access ... its nice to get an echo back. I want you and others to be good with Access, and other Office applications like Excel, Word, and PowerPoint ... and Windows. Take advantage of the strengths in each to manage your information wisely.

Are you a developer? Do you want to share? Email to ask about getting your pages added to the code index.

When we communicate, collaborate, and appreciate, we all get better. Thank you. Email me at info@msAccessGurus.com

Goto Top