Results 1 to 13 of 13
  1. #1
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116

    Using VBA to loop through a report and export each page as PDF

    Hi,



    I've managed to produce a report, which is linked to a particular query. Basically, each page of the report corresponds to each record in the query.

    What I would like to do is write VBA code, to export each individual page of the report as a separate PDF file. I'm just wondering exactly how to go about it. Can I loop through each page of the report and export to PDF that way? Or do I need to loop through the query itself, setting a parameter to produce one record at a time, save it, then export the report as a PDF file (which will consist of only one page now)? Or is there some other way?

    Thanks for your help.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Just loop through the records and print to pdf for each record.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by Welshgasman View Post
    Just loop through the records and print to pdf for each record.
    Thanks Welshgasman.

    I've already written a basic loop in VBA for a query, but I think it might need adjusting for a report. When I enter the following...

    Set rs = db.OpenRecordset("R_RemittanceAdvice")

    I get an error report "Run time error 3078. The MS db engine cannot find the input table or query. Should I change the term Recordset to something equivalent for the report?

    Thanks.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    don't use a recordset. use a query.
    the listbox has the list of those getting the report.

    the query in the report ,looks at the listbox to filter only that person/dept.
    ie: select * from table where [personID]=forms!fMyForm!lstBox
    so only data for that person in the listbox is pulled.

    then just output the report to PDF: docmd.OutputTo acOutputQuery ,acQuery,acFormatPDF,vFile

    loop thru listbox:
    Code:
    Dim i As Integer
    dim vItm
    Code:
    For i = 0 To lstBox.ListCount - 1
       vItm= lstBox.ItemData(i)  'get next item in list data
       lstBox = vItm     'set listbox to the item
     
         'now get values from field columns
       vName = lstBox.column(0)  'in vb, columns start with zero
       vEmail = lstBox.column(1)  'get email from listbox ,col.2
    
          'do stuff with it here
        vFile = "c:\temp\Report_" & vName & ".pdf"
        docmd.OutputTo acOutputQuery ,acQuery,acFormatPDF,vFile
    
          'email if needed
        DoCmd.SendObject acSendReport, "rMyReport", acFormatPDF, vEmail, , , "Subject", "message"
    Next
    

  5. #5
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks a lot ranman, I'll see if I'm able to implement your suggestion.

    Edit...I can't see where in your code where I can reference my specific report. Does it actually save my report, or just the data from the Query? My report contains a subreport, and also company logo etc.

    Sorry to ask, I'm an Access beginner.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would use the query source of the report as the recordset. I doubt you can use the report, hence the error.

    I am only suggesting this as you already have this?

    As always, more than one way to skin a cat.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  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
    Have a look at the attached code:
    Code:
    Public lCurrentID_Record As Long  'place this at the top of a standard module
    
    
    Public Function vcCurrentID_Record() As Long 'place this at the top of a standard module
    vcCurrentID_Record= lCurrentID_Record 
    'use this function in a new query based on your existing report record source
    'place the function in the criteria row of the PK ID to return one record at the time
    End Function
    
    
    '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<this is the code for the export button on your form>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    
    
    Private Sub cmdExportPDF_Click()
    
    
    On Error GoTo Err_cmdExportPDF_Click
    
    
    Dim rst As DAO.Recordset
    DIm sFileName as string
    
    
    Set rst = CurrentDb.OpenRecordset("qryYourExistingReportRecordSource", dbOpenSnapshot) 'this is the existing query returning all records to be exported
    
    
    On Error Resume Next
    
    
    DoCmd.Close acReport, "rptYourReportFiltered" 'this is a copy of your existing report with the new recordsource returning one record at the time using the public function above
    
    
    lCurrentID_Record = 0
    
    
    Do Until rst.EOF
        	lCurrentID_Record = rst("[PK_ID]") 'replace PK_ID with your own unique ID
    	sFileName="BuildYourFileNameUsingACommonPathAndUniqueInfoFromTheRecordset"	
        	DoCmd.OutputTo acOutputReport, "rptYourReportFiltered" , "PDF Format (*.pdf)", sFileName, False
      
    rst.MoveNext
    Loop
    
    
    rst.Close
    Set rst = Nothing
    
    
    Exit_cmdExportPDF_Click:
        Exit Sub
    
    
    Err_cmdExportPDF_Click
        MsgBox Err.Description
        Resume Exit_cmdExportPDF_Click
    End Sub
    Basically you create a new report based on a parameter query that returns one record at the time. The parameter can be supplied to the query in many ways (tempvar, hidden control on a form, etc.), I like to use a public function and a public variable as it is easier to follow.

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

  8. #8
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Many thanks Vlad, I'll have a look through that and see if I can make it work. I'll let you know how I get on!

  9. #9
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Check this link for demo of saving Report Pages to separate PDF Files: https://www.msaccesstips.com/2012/01...e-pdf.html?m=1

  10. #10
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by apr pillai View Post
    Check this link for demo of saving Report Pages to separate PDF Files: https://www.msaccesstips.com/2012/01...e-pdf.html?m=1
    Thanks a lot, that looks perfect. I'll give it a go.

  11. #11
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Hi again,

    I took a slight break from trying to automate the reports while I was improving my spreadsheet. I'm going to try Vlad's code above, and so I'm just attaching my practice database again in case that's useful. Vlad's code is on the Modules named "Remittance Advices". I already tweaked some of it, but I doubt I've done it right!

    I'll message Vlad in a second to ask his help, but I'll welcome any help in the meantime.

    Many thanks.
    Attached Files Attached Files

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Neil,
    Here it is, added a button to the Musician_Payments_Vlad form. Please let me know if it works for you!
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry forgot the attachment.
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 3
    Last Post: 10-04-2018, 12:26 PM
  2. Replies: 9
    Last Post: 07-07-2017, 07:39 AM
  3. Export each page of the report to a seperate PDF file
    By naeemahmad in forum Programming
    Replies: 14
    Last Post: 12-04-2013, 09:18 AM
  4. Export Report with Page Headers to Excel
    By EddieN1 in forum Reports
    Replies: 1
    Last Post: 09-01-2012, 12:37 PM
  5. Export First Page of Report for Each Unique Header
    By ertweety in forum Programming
    Replies: 3
    Last Post: 06-05-2012, 06:23 PM

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