Merging MS Access Reports 1

[Ray1127]

We have an MS Access Database Originally designed in Access 2000. There are 7 Reports Each a page in a Board of Directors Report. The reports are run in a macro to generate all of the reports and then they are each printed to PDF. The 7 PDF's are then Merged into 1 Report. Is there any way this can be automated so that the 7 reports can be exported to 1 PDF Report?

 

[lameid]

It's hard to say. If there is no code or criteria involved, I would say make the 7 reports sub reports in the same main report... You will lose page numbering this way but is an easy solution. I think you CAN set the page property in a format event so you COULD set it in the format even of each reports sub-report.... You'll just have to be sure to figure out your page breaking.

Otherwise, you could print the reports to a printer such as the old GNU PDFCreator that can be configured to append the documents to one another.... but then you still have to go in and print the batch to PDF... So this is probably only a minor improvement.

 

[Ray1127]

Thanks, that's pretty close to what I was thinking just didn't have a clue on the page breaks and numbering.

 

[sxschech]

If you are using acrobat professional, here is code to merge individual pdf files into one file. You will need to play around with the loop count depending on number of files to be merged. Code assumes that reports are named with a number as part of its name such as Report1.pdf, Report2.pdf, Report3.pdf
Of course, change the names of Report1 etc to your actual report names. I run this on a network with UNC, but changed the code here to c drive for the example.

Put this line of code after the code used to create your pdf reports and put the sub below this in its own Module.

Call MergePDF

Sub MergePDF()
    'Combined multiple PDF files into one
    'set a reference to Acrobat (Adobe Acrobat 7.0 Type Library)
    '[URL unfurl="true"]http://www.khk.net/wordpress/2009/03/04/adobe-acrobat-and-vba-an-introduction/[/URL]
    '30-JUL-2010
    
    'May need to see if file exists when this is run more than once.  Will add if needed
    
    Dim AcroApp As Acrobat.CAcroApp
    
    Dim Part1Document As Acrobat.CAcroPDDoc
    Dim Part2Document As Acrobat.CAcroPDDoc
    
    Dim numPages As Integer
    Dim pdfsrc As String
    Dim X As Integer
    Dim stMergeName As String
    Dim strundate As String
    
    Set AcroApp = CreateObject("AcroExch.App")
    
    Set Part1Document = CreateObject("AcroExch.PDDoc")
    Set Part2Document = CreateObject("AcroExch.PDDoc")
         
    pdfsrc = "c:\reports\PDFfiles\Report0.pdf"
    
    'grab run date off of form for use in the file name
    'strundate = DLookup("rundate", "tbldetail", "format(rundate,'mmmyy')=Left([Forms]![frmMain].[txtCuryr],3)  & Mid([Forms]![frmMain].[txtCuryr],5,2)")

    X = 1
    
    Part1Document.Open (pdfsrc)
    Part2Document.Open (Replace(pdfsrc, "0", X))
        
    Do While X < 8
        ' Insert the pages of Part2 after the end of Part1
        numPages = Part1Document.GetNumPages()
        
        If Part1Document.InsertPages(numPages - 1, Part2Document, 0, Part2Document.GetNumPages(), True) = False Then
            MsgBox "Cannot insert pages"
        End If
         
        X = X + 1

' Next line is a conditional if you want to exclude certain pdf files from being combined.  It is commented it out.
'        If X = 2 And Forms!frmmain.txtSemester = "Sprg" Then X = 6 'Exclude SAT Pages from Spring Report
        
        Part2Document.Close
        Part2Document.Open (Replace(pdfsrc, "0", X))
        'Debug.Print (Replace(pdfsrc, "0", x))
    Loop
    
'This part changes the file name and date. Can comment out if your file name convention is different.
     
If Forms!frmmain.txtSemester = "Fall" Then stTerm = "F" Else stTerm = "S"
    stMergeName = Replace(pdfsrc, "Report0", "Report" & Right(Me.txtCurrentYr, 2) & stTerm & "_" & Format(Me.txtRunDate, "YYYYMMDD") & "")
    
    If Part1Document.Save(PDSaveFull, stMergeName) = False Then
        MsgBox "Cannot save the modified document"
    End If
        
    Part1Document.Close
    Part2Document.Close
     
    AcroApp.Exit
    Set AcroApp = Nothing
    Set Part1Document = Nothing
    Set Part2Document = Nothing
    
' This renames one of the files unmerged files, was used for another purpose, but left code here in case it is of use

'    FileCopy stmergename, "c:\reports\pdffiles\stMergeName, "Full", "")
    
    MsgBox "Merge is Done"
    
End Sub

 

[Ray1127]

I finally figured this out and feel stupid I didn't think of this earlier. I created a blank report and then put in the 7 reports as sub reports. Had to tweak the reports a bit as Page Headers and Footers do not show up if the report is a sub report. Had 1 of the reports Report #6 was not quite a page so part of report #7 printed on the same page as #6. Moved Report #7 to the Report footer and that fixed it. Seems like there should be a better way but I could find no property to force a new page after a sub report. I am using Access 2003 maybe a later version has that ability?

 

[lameid]

There is a page break control... on the control menu it is the 6th from the right... a little triangle arror between two pages (well sort of, I hate icons). As a general rule you should not use it but this might be the exception.

You could try setting the Force new Page section properites in the sub reports... After secton on the report footer would be ideal... not sure if that will take in a sub report.