none
Export Report Into Individual PDF's RRS feed

  • Question

  • I have a report with loads of fields from multiple tables. Each report fits onto one page and I am trying to export each page as a separate PDF by the Legal Name from the Table Fund List. I have the basic VBA that exports the whole report onto my desktop. Ill post below. 

      I barley know anything about VBA, i know that I will have to put a loop in but I am not able to figure it out. I have seen the other thread on here about this but I am not able to follow due to my lack of VBA skills.

    I will also post the SQL for my report. My report has lots of fields from every one of my tables (5 tables) hopefully this is enough information. Thanks

    Public Sub ExportFilteredReportToPDF()

        Dim reportName As String
        Dim criteria As String
        Dim Entity As String

        Entity = "Legal Entity"
        reportName = "Report1"
        criteria = "Legal Entity"

            DoCmd.OpenReport reportName, acViewPreview, , , WindowMode:=acHidden
            DoCmd.OutputTo acOutputReport, reportName, "PDFFormat(*.pdf)", , False, "", , acExportQualityPrint
            DoCmd.Close acReport, reportName, acSaveNo

    End Sub

    SELECT [USD Instructions].[Legal Name], [USD Instructions].[Beneficiary Bank Name], [USD Instructions].[Bank ABA#], [Fund List2].[Tax ID #], [Fund List2].[Bank Loan Fax], [Fund List2].[Bank Loan Email], [USD Instructions].SWIFT, [USD Instructions].[Bene Act Nm], [USD Instructions].ActDDA, [USD Instructions].[FFC Name], [USD Instructions].[FFC Account], [Fund List2].[Address Line 1], [Fund List2].[Address Line 2], [Fund List2].[Address Line 3], [Fund List2].[Address Line 4], [Fund List2].[Address Line 5], [Fund List2].[FFC/FBO], [GBP Instructions].[Intermediary Bank Name], [GBP Instructions].[Intermediary BIC], [GBP Instructions].[CHAPS Sort Code], [GBP Instructions].[Beneficiary Bank Name] AS [Beneficiary Bank Name_GBP Instructions], [GBP Instructions].[Beneficiary Account Number], [GBP Instructions].[Beneficiary Account Name], [GBP Instructions].[Beneficiary BIC], [GBP Instructions].[FFC Name] AS [FFC Name_GBP Instructions], [GBP Instructions].[FFC Account] AS [FFC Account_GBP Instructions], [EUR Instructions].[Intermediary Bank Name] AS [Intermediary Bank Name_EUR Instructions], [EUR Instructions].[Intermediary BIC] AS [Intermediary BIC_EUR Instructions], [EUR Instructions].[Intermediary Account Number], [EUR Instructions].[Beneficiary Bank Name] AS [Beneficiary Bank Name_EUR Instructions], [EUR Instructions].[Beneficiary BIC] AS [Beneficiary BIC_EUR Instructions], [EUR Instructions].[Bene Act Nm] AS [Bene Act Nm_EUR Instructions], [EUR Instructions].[Beneficiary Account #], [EUR Instructions].[FFC Name] AS [FFC Name_EUR Instructions], [EUR Instructions].[FFC Account] AS [FFC Account_EUR Instructions], [AUD Instructions].[Intermediary Bank Name] AS [Intermediary Bank Name_AUD Instructions], [AUD Instructions].[Intermediary BIC], [AUD Instructions].[Intermediary Bank Account], [AUD Instructions].[Beneficiary Bank Name] AS [Beneficiary Bank Name_AUD Instructions], [AUD Instructions].[Beneficiary BIC1], [AUD Instructions].[Beneficiary Account Name] AS [Beneficiary Account Name_AUD Instructions], [AUD Instructions].[Beneficiary Account Number] AS [Beneficiary Account Number_AUD Instructions], [AUD Instructions].[FFC Name] AS [FFC Name_AUD Instructions], [AUD Instructions].[FFC Account] AS [FFC Account_AUD Instructions], [CAD Instructions].[Intermediary Bank Name] AS [Intermediary Bank Name_CAD Instructions], [CAD Instructions].[Intermediary BIC] AS [BIC_CAD Instructions], [CAD Instructions].[Intermediary Bank Account] AS [Intermediary Bank Account_CAD Instructions], [CAD Instructions].[Beneficiary Bank Name] AS [Beneficiary Bank Name_CAD Instructions], [CAD Instructions].[Beneficiary BIC] AS [BIC1_CAD Instructions], [CAD Instructions].[Beneficiary Account Name] AS [Beneficiary Account Name_CAD Instructions], [CAD Instructions].[Beneficiary Account Number] AS [Beneficiary Account Number_CAD Instructions], [CAD Instructions].[FFC Name] AS [FFC Name_CAD Instructions], [CAD Instructions].[FFC Account] AS [FFC Account_CAD Instructions], [CAD Instructions].Reference, [Fund List2].[Legal Entity], [Fund List2].[EuroClear #], [Fund List2].[Bank Name], [Fund List2].[DTC #], [Fund List2].[Agent ID], [Fund List2].[Institution ID]
    FROM (((([Fund List2] INNER JOIN [USD Instructions] ON [Fund List2].[Fund Number] = [USD Instructions].[Fund Number]) LEFT JOIN [GBP Instructions] ON [Fund List2].[Fund Number] = [GBP Instructions].[Fund Number]) LEFT JOIN [EUR Instructions] ON [Fund List2].[Fund Number] = [EUR Instructions].[Fund Number]) LEFT JOIN [AUD Instructions] ON [Fund List2].[Fund Number] = [AUD Instructions].[Fund Number]) LEFT JOIN [CAD Instructions] ON [Fund List2].[Fund Number] = [CAD Instructions].[Fund Number];


    Wednesday, February 6, 2019 7:56 PM

All replies

  • Hi. Several approaches is possible, but the main point is to have the report only display one record, so it can be exported to PDF. In any scenario, you would have to loop through the records to export each one as a PDF. So, you can either use a parameter query to filter the report to one record or use the WhereCondition argument of the OpenReport method to filter the report, but you'll still have to open the report in sequence for each record to export them into PDF.
    Wednesday, February 6, 2019 8:00 PM
  • The report already displays one record, each field is linked to the Legal Name on the main , Fund List table. My report already exports a page for each legal name in my tables. the issue i have is getting each one of those pages of the report to export as an individual pdf
    Wednesday, February 6, 2019 8:26 PM
  • The report already displays one record, each field is linked to the Legal Name on the main , Fund List table. My report already exports a page for each legal name in my tables. the issue i have is getting each one of those pages of the report to export as an individual pdf

    Hi. I'm not sure I follow. Are you saying the whole report only displays information from one record? If not, then what I said earlier applies. Otherwise, you could just simply open the report as many times as you have records to export to PDF. You would do this in a loop.
    Wednesday, February 6, 2019 8:46 PM
  • With your current approach of opening the report transparently you would need to loop through a recordset of all Legal Name values and, at each iteration of the loop, filter the report on the Legal Name column equalling the value returned by the recordset's current row, by means of the WhereCondition argument of the OpenReport method.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, February 6, 2019 10:54 PM Typo corrected.
    Wednesday, February 6, 2019 10:52 PM
  • From what theDBguy wrote, no sorry the whole report is 300 pages with each page designated to one Legal Entity, 

    I understand that I need to insert a loop throughout my report i am just not sure how to set up the VBA for that. I have no experience with loops. 

    If someone could help me with maybe a little starter VBA i would appreciate it. Thanks.

    Thursday, February 7, 2019 12:52 AM
  • Hi gouda. Thanks for the additional information. But since we are not familiar with your database, I am not sure I completely understand what you just said yet. For example, you said the report you have is for one Legal Entity but contains 300 pages. So, are you asking how to split those 300 pages into separate PDF files per page? If so, is there some sort of data divider to tell us when a page starts and ends? For instance, if a "Legal Entity" is like a Purchase Order and the 300 pages where like the line items for this order, then we can use the LineItemOrderID to split the line items into pages.
    Thursday, February 7, 2019 4:09 PM
  • oh sorry for the confusion, 

    so the unique identifier for each Legal Entity is a "FundNumber" that is unique for each Legal Entity. Hopefully that helps. Wold a screen shot of my report in design view help as well?

    Each Page is broken up by each fund number and there are 300 funds. I want the report broken up into each page and exported into a PDF with the title of the PDF being the "Legal Entity" Name that is associated with each "FundNumber"

    Thanks again.

    Thursday, February 7, 2019 7:36 PM
  • Okay, so I think you're saying each FundNumber data can fit into one page of the report. Correct? If so, you're asking how to create 300 PDFs (one for each FundNumber). Right? If so, then it's like I was saying at the beginning, you can filter the report so it will only display one page (i.e. one FundNumber). For example, if we execute the following command in the Immediate Window, we should see the entire report with 300 pages.

    DoCmd.OpenReport "ReportName", acViewPreview

    However, if we add a specific FundNumber in the WhereCondition argument, then we should only see one page.

    DoCmd.OpenReport "ReportName", acViewPreview, , "FundNumber=12345"

    So, what we'll need to do is loop through all 300 FundNumbers and execute the above statement (or the OutputTo command to create the PDF) to create 300 separate files.

    Does it make sense?

    Thursday, February 7, 2019 8:51 PM
  • yes that is exactly right. 

    So I understand that the Open Report command and adding a filter to specifically output a specific fund number but the part I dont understand is how to create a loop.

    I am not familiar with the loop function or a way to set up the layout.

    Friday, February 8, 2019 5:27 PM
  • Fortunately, we are already ready to understand what you mean. Now, according to me, there is a risk of misunderstanding. The way theDBGuy has explained to you is good for you and you understand that.

    However, it is not necessary now to create a loop. What you have to take care of is that the value of FundNumber is taken from somewhere, for example from a loaded form. Suppose the name of the form is MyForm and the value is displayed in a control called FundNumber. Then use the following example. This assumes that the field FundNumber in the table (which value is shown in the control) is of a numeric type.

    DoCmd.OpenReport "ReportName", acViewPreview, , "FundNumber=" & Forms!MyForm!FundNumber

    Friday, February 8, 2019 8:57 PM
  • Prayers for your well-being, Ken

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, February 12, 2019 8:16 PM
  • yes that is exactly right. 

    So I understand that the Open Report command and adding a filter to specifically output a specific fund number but the part I dont understand is how to create a loop.

    I am not familiar with the loop function or a way to set up the layout.

    Hi... PNR must know something I don't, I hope Ken is okay. So, based on Ken's idea of using a loop, here's one way to accomplish it. Let's say the FundNumbers are stored in a table called tblFundNumbers, this code will loop through all the records in that table and use each value as a filter to open the report and output it to PDF.

    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strFN As String 'assuming Fund Number is a Text data type
    
    strSQL = "SELECT FundNumber FROM tblFundNumbers"
    
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
    With rs
        Do While Not .EOF
            strFN = !FundNumber
            DoCmd.OpenReport "ReportName", acViewPreview, , "FundNumber='" & strFN & "'", acHidden
            DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, currentproject.path & "\" & strFN & ".pdf"
            DoEvents
            DoCmd.Close acReport, "ReportName"
            .MoveNext
        Loop
        .Close
    End With
    
    Set rs = Nothing

    (untested) - You might get some timing issue because executing this code, looping through 300 records, will be faster than creating 300 PDF files. It might be a good idea to test it out first with a smaller table until you get it to work.

    Tuesday, February 12, 2019 8:52 PM
  • Ken posted in another thread that he was facing some health issues.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, February 12, 2019 8:59 PM
  • Ken posted in another thread that he was facing some health issues.

    peter n roth - http://PNR1.com, Maybe some useful stuff


    Hi. Thanks for the update. Hope he gets well soon!
    Tuesday, February 12, 2019 9:00 PM