none
Access 2007 - "Save as PDF" from VBA / automation

    Question

  • Hi there,

    I would like to render a particular access Report in my MDB as a PDF - either by automation from VB6 or from Access VBA.

    I have downloaded the "Save as PDF / XPS" add-on for Office 2007, so can manually save a report as PDF from Access now.

    However, attempts to work out how to do this in code have so far drawn a blank. RunCommand does not seem to be able to access this particular functionality.

    Could anybody point me in the direction of a solution?

    Many thanks.

    Richard Fewster
    Thursday, March 22, 2007 11:12 AM

Answers

  • Hi,

     

    What you will need to do is create a macro with the

    Action

    'OutputTo'

    Arguments

    'Report, Report Name, PDF Format (*.pdf), , Yes, , 0, Print'

     

    There is a number of options within the 'arguments' section.

     

    Ekky

     

    PS. I hope this works once I create a runtime version of my the database although I am guessing that it won't.

    Tuesday, March 27, 2007 10:37 AM

All replies

  • Hi,

     

    What you will need to do is create a macro with the

    Action

    'OutputTo'

    Arguments

    'Report, Report Name, PDF Format (*.pdf), , Yes, , 0, Print'

     

    There is a number of options within the 'arguments' section.

     

    Ekky

     

    PS. I hope this works once I create a runtime version of my the database although I am guessing that it won't.

    Tuesday, March 27, 2007 10:37 AM
  • PDF output command has a problem, though; it can't provide filtering by itself. There's no filter clause, not a where statement you can play with.

    After browsing tons of webpages, my best VBA approach is as follows:

    Dim MyFilter as String
    Dim MyPath as String
    Dim MyFilename as String

    MyFilter = "Field = State your filter here as usual"

    'I place the file on a different folder depending on the date stated in a field called "Date"
    MyPath= "C:\Folder\" & Format(Me.Date, "yyyy")

    'State the filename. Here I use YYYY-DD-MM-Event.pdf

    MyFilename= Format(Me.EventDate, "yyyy") & _
                     "-" & Format(Me.EventDate, "dd") & Format(Me.EventDate, "mm") & _
                     "-" & Me.Event & ".pdf"

    'Let's print and save. Once you see it works, you can change True to False so that the file created is not opened after completion.
    DoCmd.OpenReport "ReportName", acViewPreview, , MyFilter
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, True

    'Let's close our previewed report
    DoCmd.Close acReport, "ReportName"

    The thing is, as I said before, that you can' filter by the OutputTo line. However, HelpFile for that function says you'll need to state the object type and leave the ObjectName field blank if you want to print the active object (that's why I've got to open the form on preview mode and then close it. If you try acViewNormal it'll throw an error). It might not be the sharpest encoding on Earth, but it works...

    Hope that helps.

    Monday, February 23, 2009 10:24 PM
  • Mariucci,

    That worked well for me.  Thanks for your post.

    Troy
    Thursday, July 16, 2009 5:51 PM
  • I've been searching (on & off) for this solution for quite a while ... it does exactly what I need ... thanks!
    Tuesday, August 11, 2009 11:45 AM
  • Thank you Mariucci! This works great and handles the filtering! The only thing I can add is that you need to end your MyPath assignment with a back slash so that  MyPath & MyFilename gets separated.
    Sunday, December 27, 2009 11:01 PM
  • The solution posted by Mariucci is key. Automating Access 2007 reports to create custom PDF files is such a valuable solution (at least for myself). If you can master this stuff here, you have some true RAD development tools that give impressive results.
    Wednesday, January 27, 2010 4:24 PM
  • After much looking and looking at  a lot of coding I have come up with this short function
    Using Acrobat Pro 8 on Windows 7 with Access 2007

    There is scope within this line to add further control if required


    Function PrintToPDF(SrcFile As String, DestPath As String, DestFile As String, ShowPdf As Boolean)
    On Error GoTo PrintToPDF_Err

    'ScrFile = The report Name to output as PDF
    'DestPath = Destination path for PDF file
    'DestFile = File name for the PDF file being created without the file extension
    'Showpdf = launch acrobat and display the PDF file or not


        DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint

    PrintToPDF_Exit:
        Exit Function

    PrintToPDF_Err:
        MsgBox Error$
        Resume PrintToPDF_Exit

    End Function

    PrintToPDF_Err:
        MsgBox Error$
        Resume PrintToPDF_Exit

    End Function
    Tuesday, February 09, 2010 11:26 PM
  • I combined the two best solutions posted, GTDocshop's and Mariucci's, using Mariucci's customization and GT's function format. Works like a charm.


    Option Compare Database
    Option Explicit
    
    Function PrintToPDF(SrcFile As String)
    On Error GoTo PrintToPDF_Err
    'Function can be called from any report with this: "PrintToPDF(Screen.ActiveForm.Name)"
    
    'SrcFile = name of report the function was called from, as generated by Screen.ActiveForm.Name
    'DestPath = Destination path for PDF file
    Dim DestPath As String
    'DestFile = Destination file name for PDF file
    Dim DestFile As String
    'ShowPdf = launch acrobat and display saved PDF file
    Dim ShowPdf As Boolean
    
    'Saves the file to the desktop of the current user
    DestPath = "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
    'Formats the file name like this: "YYYY-MM-DD-ReportNameHere.pdf"
    DestFile = Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "-" & SrcFile
    
    ShowPdf = False
    DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint
    
    PrintToPDF_Exit:
        Exit Function
    
    PrintToPDF_Err:
        MsgBox Error$
        Resume PrintToPDF_Exit
    
    End Function
    
    Thursday, February 18, 2010 8:47 PM
  • Hi,

    I had the same problem as you. So i decided to do a little internet sniffing. I use 1 form for reports combined with a table. The form has a listbox with names from reports (list of telephones, list of members, ...). there are also 3 buttons on the form namely showreport, printreport and savereport.

    then there is code behind the form and the actual report. The report is based on a query/table. then the recordsource and the filter of the report are deleted, so when you execute the report it is completely blank accept for the database fields (but they are in error --> #Name?). the titel of the report is a label named lblreporttitle

    The rest is done as follows :

    table-built up :
    - report_ID
    - reportname
    - displayname
    - where
    - query
    - sort order
    - report title
    - destination_file
    - acrobat_show (this also works with other pdf programs lile for instance pdf exchange viewer)
    - output_type

    code behind the report :

    Private Sub Report_Open(Cancel As Integer)
    On Error GoTo Err_Report_Open

        Me.RecordSource = Me.OpenArgs
        Me.OrderBy = GlobalOrderBy
        Me.OrderByOn = True
        Me.lblReportTitle.Caption = GlobalReportTitle
        
    Exit_Report_Open:
        Exit Sub

    Err_Report_Open:
        MsgBox Err.Description
        Resume Exit_Report_Open
        
    End Sub

    Code behind the form :

    Option Compare Database
    Option Explicit

    Dim strRapportnaam As String --> report name
    Dim strWhere As String
    Dim strQuery As String
    Dim strPath As String
    Dim strFilename As String
    Dim boolShowPDF As Boolean
    Dim strOutput As String

    Dim DB As DAO.Database
    Dim RS As DAO.Recordset

    Private Sub btnAfdrukken_Click() --> "print report"
    On Error GoTo Err_btnAfdrukken_Click

        DoCmd.OpenReport strRapportnaam, acViewNormal, strQuery, strWhere, , strQuery

    Exit_btnAfdrukken_Click:
        Exit Sub

    Err_btnAfdrukken_Click:
        MsgBox Err.Description
        Resume Exit_btnAfdrukken_Click
        
    End Sub
    Private Sub btnAfdrukvoorbeeld_Click() --> "show report"
    On Error GoTo Err_btnAfdrukvoorbeeld_Click

        DoCmd.OpenReport strRapportnaam, acViewReport, strQuery, strWhere, , strQuery

    Exit_btnAfdrukvoorbeeld_Click:
        Exit Sub

    Err_btnAfdrukvoorbeeld_Click:
        MsgBox Err.Description
        Resume Exit_btnAfdrukvoorbeeld_Click
        
    End Sub

    Private Sub Form_Deactivate()
    On Error GoTo Err_Form_Deactivate

        RS.Close
        DB.Close

    Exit_Form_Deactivate:
        Exit Sub

    Err_Form_Deactivate:
        MsgBox Err.Description
        Resume Exit_Form_Deactivate

    End Sub

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Form_Open

        Set DB = CurrentDb
        Set RS = DB.OpenRecordset("tbl_Reports", dbOpenDynaset)

    Exit_Form_Open:
        Exit Sub

    Err_Form_Open:
        MsgBox Err.Description
        Resume Exit_Form_Open

    End Sub

    Private Sub lbKeuzeRapporten_AfterUpdate() --> "listbox"
    On Error GoTo Err_lbKeuzeRapporten_AfterUpdate

        RS.FindFirst "[Report_ID] = " & Me.lbKeuzeRapporten
        If Not RS.EOF Then
        
            If Not IsNull(RS("Reportname")) Then
                strRapportnaam = RS("reportname")
            End If
            
            If Not IsNull(RS("Where")) Then
                strWhere = RS("Where")
            End If
            
            If Not IsNull(RS("Query")) Then
                strQuery = RS("Query")
            End If
            
            If Not IsNull(RS("sort order")) Then
                GlobalOrderBy = RS("sort order")
            End If
            
            If Not IsNull(RS("reporttitle")) Then
                GlobalReportTitle = RS("reporttitle")
            Else
                If IsNull(RS("reporttitle")) Then
                    GlobalReportTitle = ""
                End If
            End If
            
            If Not IsNull(RS("destination_File")) Then
                strPath = RS("destination_File")
            End If
            
            If Not IsNull(RS("display name")) Then
                strFilename = RS("display name")
            End If
            
            If Not IsNull(RS("Output_type")) Then
                strOutput = RS("Output_type")
            End If
            
            boolShowPDF = RS("Acrobat_Show")
            
        End If

    Exit_lbKeuzeRapporten_AfterUpdate:
        Exit Sub

    Err_lbKeuzeRapporten_AfterUpdate:
        MsgBox Err.Description
        Resume Exit_lbKeuzeRapporten_AfterUpdate
        
    End Sub

    Private Sub btnOpslaan_Click() --> "save report"
    On Error GoTo Err_btnOpslaan_Click
        
        DoCmd.OpenReport strRapportnaam, acViewReport, strQuery, strWhere, , strQuery
        DoCmd.OutputTo acOutputReport, strRapportnaam, strOutput, strPath & strFilename & ".pdf", boolShowPDF, "", 0, acExportQualityPrint

    Exit_btnOpslaan_Click:
        Exit Sub

    Err_btnOpslaan_Click:
        MsgBox Err.Description
        Resume Exit_btnOpslaan_Click
        
    End Sub


    Sunday, February 21, 2010 11:28 AM
  • My issue seems somewhat different. The purpose of my database is to review documents and "score" for quality assurance. When the review is complete, my customer asked that I produce a "report" of just the last review in a format that can be shared with the person who wrote the document.

    I programmed when the review is complete, the record is saved. (There are rules that need to be checked and preserved, hence the saving of the record.) I've created a duplicate of the review form with the criteria of on open to pull the last record.

    My problem occurs when I print to the PDF. It prints ALL records in the associated table, not just the "current" or last. I can't find a Argument on the OutputTo command for only selected or only last.

    Hope my question is clear.

    Tuesday, September 18, 2012 7:47 PM
  • Thanks for the solution.

    How would you control the quality and size of the priint, do you have to reset printer settings or change default print setting? Since the report I am trying to create as a pdf requires it to be landscape and on legal paper.

    Thanks

    Friday, September 21, 2012 2:35 PM
  • there is another way to save as PDF using macro .

    here is link : <url> http://accessguru.net/Articles_MSAccess/0033-Macro%20to%20save%20a%20report%20as%20PDF%20and%20email%20to%20Microsoft%20outlook.php</url>

    Best regards

    AccessGuru


    • Proposed as answer by AccessGuru Wednesday, July 09, 2014 9:52 AM
    • Edited by AccessGuru Monday, July 21, 2014 9:09 AM
    Tuesday, July 08, 2014 4:24 PM
  • AS above i say using macro we can save in PDF format using MACRO.

    here is link : <url> http://accessguru.net/Articles_MSAccess/0033-Macro%20to%20save%20a%20report%20as%20PDF%20and%20email%20to%20Microsoft%20outlook.php</url>

    Best regards

    AccessGuru

    Tuesday, July 08, 2014 4:25 PM