?
Solved

Save to PDF in VBA

Posted on 2013-12-09
4
Medium Priority
?
1,468 Views
Last Modified: 2013-12-25
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?
0
Comment
Question by:ICTIndika
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 39705648
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) & "\"
    Else
        sDirectory = sDirectory
    End If

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

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

    With clsPDF
        .Init
        .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

    DoEvents
    DoCmd.OpenReport rptName, acViewNormal, , sFilterCriteria
    DoEvents

    If blnPrinterChanged Then SetDefaultPrinter strDefaultPrinter

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

End Function
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39706064
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.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 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