Results 1 to 7 of 7
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Print several reports into one PDF

    I'm trying to edit the below code to meet a new challenge. Previously, it would print a merchant packing list from one merchant, no problem.

    Problem: I'd like to print to one PDF file all merchant packing lists. Does anyone have a sample of this? As I see it, I would have to print one packing list to a PDF, then loop through a record set adding pages to the PDF. Is it possible to add pages to an existing PDF using a module? As you might expect, with each record I'm overwriting the file for each record. Each merchant has their own custom packing list. So it's not like I can create a single report with all merchants then print it. I would be looping through each merchants packing list and adding to the PDF.



    Can anyone point me in the right direction? No doubt I could be missing something that would be much easier.


    Code:
    Public Function PackingLists_CA()
    
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim varReportName As String
        Dim varReportFileName As String
        Dim varPLModule As String
        Dim temp As String
        Dim PackListQuery As String
        Dim ReportName As String
    
    
        PackListQuery = "qry_PackingLists_Modules_CA"
            
        
        Set db = CurrentDb()
    
    
        Set rs = db.OpenRecordset("SELECT Distinct [ReportName],[ReportFileName] FROM " & PackListQuery, dbOpenSnapshot)
    
    
        Do While Not rs.EOF
    
    
            varReportName = rs("ReportName")
            varReportFileName = rs("ReportFileName") & ".pdf"
    
    
            'acViewPreview = Print Preview, acViewReport = Direct to Printer
            DoCmd.OpenReport varReportName, acViewPreview, , "[FileNam]='" & varReportFileName & "'"
            DoCmd.OutputTo acOutputReport, "", acFormatPDF, varReportFileNam
            DoCmd.Close acReport, varReportName
            DoEvents
    
    
            rs.MoveNext
        Loop
    
    
    
    
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
    
    End Function
    thanks!

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Unless someone has a better suggestion (very likely) I would loop through and export each report I needed then use a command line tool I can execute from vba to merge them. I personally use a program called pdftk.

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I use pdftk as well. Normally, it's a godsend. The downside for this project is it could be a mix of hundreds of reports. This is because I need to sort the end results by product, not merchant. It's easier for the warehouse to grab the top 'x' amount of packing lists for SKU1, then the next worker can grab a stack of SKU2, etc.

    I've been creating the PDF's per each merchant, then assembling them into one PDF with
    pdftk. Unfortunately, the warehouse has to pick through the mix which costs time.

    Thanks!

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I see. You can execute shell commands from vba to automate pdftk. If you want to try that and need some help let me know.

  5. #5
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Just curious, too lazy to did through the commands for pdftk. Does pdftk have the ability to analyze, say 100 files in a folder, and assemble them into one PDF in numerical or alphabetical order? That would save me from having to string all the filenames together.

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Analyze how exactly? You mean just sort the PDF's by filename? I doubt pdftk will do that but you could certainly sort the filenames in vba before you combine them. pdftk combines them in the order that they're given.

    Here is a listing of all the pdftk command line options: https://www.pdflabs.com/docs/pdftk-man-page/


    Here's some code I've googled up, I haven't necessarily tried all of these, I'm just offering them as examples.

    Use vba to read filenames in a directory: https://www.automateexcel.com/vba/list-files-in-folder/
    Sort an array with VBA: https://stackoverflow.com/questions/...-sort-function
    Run shell commands from VBA: https://www.myonlinetraininghub.com/vba-shell
    Run shell commands from VBA AND wait for the command to finish before returning to your app: https://stackoverflow.com/questions/...nd-to-complete (this is usually how I do it)

    If you end up having to run a bunch of command lines to get your final result it might be a good idea to do some error checking along the way http://www.allenbrowne.com/func-11.html

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Why not embed all them as subreports in one big report and save that as PDF?

    Here is one example how to do that dynamically:http://www.utteraccess.com/forum/lof.../t2023763.html

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Print Reports as PDF
    By SierraJuliet in forum Reports
    Replies: 2
    Last Post: 01-31-2019, 06:17 PM
  2. Replies: 2
    Last Post: 02-15-2015, 11:36 AM
  3. Reports only print once
    By Perceptus in forum Reports
    Replies: 3
    Last Post: 10-02-2014, 10:10 AM
  4. Reports print Twice
    By brharrii in forum Reports
    Replies: 5
    Last Post: 10-31-2012, 02:22 PM
  5. Print different Reports at once
    By Brian62 in forum Reports
    Replies: 5
    Last Post: 01-21-2011, 11:19 AM

Posting Permissions

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