Solved

Save to PDF in VBA

Posted on 2013-12-09
4
1,356 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
4 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

789 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