• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1532
  • Last Modified:

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?
0
ICTIndika
Asked:
ICTIndika
1 Solution
 
MacroShadowCommented:
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
 
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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now