Solved

Save to PDF in VBA

Posted on 2013-12-09
4
1,262 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 26

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now