Save to PDF in VBA

I have some reports that I run daily.  Is it possible to have these reports saved to PDF file format and saved to my c:drive through VBA code?
Who is Participating?
MacroShadowConnect With a Mentor Commented:
In 2010 and up (or 2007 with the free Microsoft add-on) you can use OutputTo:
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "C:\Report.pdf", True

Open in new window

Another option is using a third party pdf printer, my personal preference is BullZip. Here is some code from one of my projects that I regularly use. (There is some additional code you will need if you want to use it, if your intrested let me know and I'll upload it, in the meantime this code is just a proof of concept).

Option Explicit
Option Compare Database

Public Declare Function SetDefaultPrinter Lib "winspool.drv" _
                                          Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long

'Uses early binding so you must add a reference to BullZip

Function PrintReportAsPDFwithBullZip(ByVal rptName As String, _
                                     Optional sFilterCriteria As String = "", _
                                     Optional sDirectory As String = "", _
                                     Optional sFileName As String = "") _
                                     As Boolean

    On Error GoTo err_Error

    Dim clsPDF As New Bullzip.PDFPrinterSettings  'Initialize the PDF class

    Dim strDefaultPrinter As String
    Dim blnPrinterChanged As Boolean

    'set the success flag to true here but it will be set to
    'false if the function fails at any point
    PrintReportAsPDFwithBullZip = True

    If sDirectory = "" Then
        sDirectory = GetSpecialFolder(CSIDL_LOCAL_APPDATA) & "\" & FileNameNoExt(GetDBName) & "\"
        sDirectory = sDirectory
    End If

    If sFileName = "" Then
        sFileName = FileNameNoExt(GetDBName)
        sFileName = sFileName
    End If

    If LCase(Right(sFileName, 4)) <> ".pdf" Then
        sFileName = sFileName & ".pdf"
    End If

    With clsPDF
        .SetValue "Output", sDirectory & sFileName
        .SetValue "ShowSettings", "never"
        .SetValue "ShowPDF", "no"
        .SetValue "ConfirmOverwrite", "no"
        .SetValue "SuppressErrors", "yes"
        .SetValue "ShowProgress", "no"
        .SetValue "ShowProgressFinished", "no"
        .SetValue "Author", "Me"
        .SetValue "Title", "My Reports"
        .SetValue "My Subject"
        .WriteSettings (True)    'writes the settings in a runonce.ini that is immediately deleted after being used.
    End With

    If InStr(Application.Printer.DeviceName, "BullZip") = 0 Then    ' If BullZip isn't the default printer
        blnPrinterChanged = True                                    ' Set the printer changed flag to true
        strDefaultPrinter = Application.Printer.DeviceName          ' Save name of current printer
        SetDefaultPrinter "Bullzip PDF Printer"                     ' Use API to set the Current printer to Bullzip
    End If

    DoCmd.OpenReport rptName, acViewNormal, , sFilterCriteria

    If blnPrinterChanged Then SetDefaultPrinter strDefaultPrinter

    'error handler and exit
    Set clsPDF = Nothing
    Exit Function
    PrintReportAsPDFwithBullZip = False
    MsgBox Err.Description
    Resume err_Exit

End Function
Vadim RappCommented:
Regarding 3rd party pdf printer: if this particular report is going to be always printed to PDF and to be used on computers with the same PDF printer installed, you can do it even easier by specifying "specific printer" in report's Page Setup. Or you can have two copies of the same report, one going to Default Printer, another to Specific Printer, and choose one or another in code.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.