Results 1 to 4 of 4

Thread: Export Report to Single PDF Pages

  1. #1
    02kev02 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    2

    Question Export Report to Single PDF Pages

    Hi,

    I'm very new to access and VBA.
    I'm hoping someone has a solution or can provide a little bit of guidance.

    I would like to be able to export my reports so that each record is exported to a seperate PDF file and titled by the unique identifier for example.
    In Access, I only have the option of exporting ranges of pages, or the whole report combined.
    However, this makes it difficult when there are over 500 records that need to be seperated.
    Is there some sort of script that can automize this process?

    Thanks to anyone who can help!!

    Kevin

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,130
    My approach would be a VBA procedure that opens a recordset of the unique identifier and loops through the recordset and output PDF of report filtered by the identifier from the recordset. I recently did something quite similar to output PDFs of about 70,000 reports from legacy database. An extract of code:
    Code:
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    rs.Open "SELECT LabNum FROM Submit;", cn, adOpenStatic, adLockPessimistic
    While Not rs.EOF
          DoCmd.OpenReport "LabReport", acViewPreview, , "LabNum='" & rs!LABNUM & "'"
          DoCmd.OutputTo acOutputReport, "", acFormatPDF, "drive:\path\" & rs!LABNUM & ".pdf", True
          DoCmd.Close acReport, "LabReport", acSaveNo
          'kill Adobe Acrobat so I don't have thousands of open pdf documents
          Dim objWMIService As Object, objProcList As Object, objProcess As Object
          Set objWMIService = GetObject("winmgmts:")
          Set objProcList = objWMIService.ExecQuery("Select * from Win32_Process Where Name = 'acrobat.exe'")
          For Each objProcess In objProcList
               objProcess.Terminate (0)
          Next
          rs.MoveNext
    Wend
    I couldn't actually output all 70,000 reports in one batch run. Had to do it in groups of about 5,000, otherwise Access would crash.

    EDIT: I have since discovered the 'kill' code is not need if the True parameter of OutputTo is changed to False. The 6 lines of code following the 'kill' comment are then not needed.
    Last edited by June7; 10-30-2012 at 02:13 PM.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    02kev02 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    2
    Thank you so much for your quick reply!!
    I will have to dive into VBA and try to implement this

  4. #4
    Fuzz_cozens is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    10
    Hi Wendy

    See code below for you to have a look at:

    Private Sub Command92_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "Commission Statement - 1Life Agent Summary;", cn, adOpenStatic, adLockPessimistic
    While Not rs.EOF
    DoCmd.OpenReport "brokerage commission statement - 1Life", acViewPreview, , "LabNum='" & rs!LABNUM & "'"
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, "C:\Stats Report\" & rs!brokerage & ".pdf", False
    DoCmd.Close acReport, "brokerage commission statement - 1Life", acSaveNo
    rs.MoveNext
    Wend


    I am getting an error "User defined type not defined" for the 2nd line of code which is "Dim cn As ADODB.Connection" I am not sure what this is. Please can you check the rest of the code for me bearing in mind the report name is

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-08-2012, 11:03 AM
  2. Export single record from a form to PDF
    By GraemeG in forum Programming
    Replies: 1
    Last Post: 04-10-2011, 12:33 PM
  3. Exporting single query and single report.
    By rfhall50 in forum Programming
    Replies: 2
    Last Post: 02-18-2011, 10:08 AM
  4. Replies: 0
    Last Post: 05-13-2010, 04:18 AM
  5. Blank pages between report pages
    By jonsuns7 in forum Reports
    Replies: 2
    Last Post: 10-01-2009, 04:06 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums