Merge reports to single pdf 2

[primagic]

I am trying to merge multiple reports to a single pdf. I have looked at stephen labans database but it doesnt really help me out.

I have 15 checkboxes on a form and a command button that will take each of the ticked checkboxes and output them to pdf.

I want to then be able to merge all those reports to one pdf. Or can I output all the selected reports to one pdf?

Thanks

 

[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

 

[primagic]

Hi thanks for the code. Works well. However, I have used the code from the link in the credits.

Code below is:

Dim AcroApp As Acrobat.CAcroApp
    
    Dim Part1Document As Acrobat.CAcroPDDoc
    Dim Part2Document As Acrobat.CAcroPDDoc
    
    Dim numPages As Integer
    
    Set AcroApp = CreateObject("AcroExch.App")
    
    Set Part1Document = CreateObject("AcroExch.PDDoc")
    Set Part2Document = CreateObject("AcroExch.PDDoc")
     
    Part1Document.Open ("C:\rpt1CasesByWHContact.pdf")
    Part2Document.Open ("C:\rpt2CasesByClientContact.pdf")
    
    ' 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
     
    If Part1Document.Save(PDSaveFull, "C:\MergedFile.pdf") = 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
     
    MsgBox "Done"

This works for 2 documents, but I could have up to 15 documents created. And all 15 will not always be created. There could be 1 or 5 or 9 pdf's.

So how would I skip the pdf's that are not created?

Thanks again

 

[sxschech]

There are several ways you could do it. If you know ahead of time the number of reports to merge, you could use the loop method I provided in the original post and change the '8' in

Do While X < 8

to something like

Do While X < forms!frmReport.txtNoOfReports

to another variable or point to a text box on your form that would hold the count. So if you had 5 reports, you would enter a 5 in the text box.

Alternatively, you could use code to check if the file exists and then skip that report if it doesn't. In code below, if the file doesn't exist it will be ="".

If Dir(stPath & stFileName) = "" Then

 

[primagic]

If I use your code, where do you list all the pdf documents to merge?

I can only see one.

pdfsrc = "c:\reports\PDFfiles\Report0.pdf"

 

[sxschech]

The way I set up my report list, I didn't need to create a list because I output the individual reports using a standard name such as Report0, Report1, Report2, etc (Report would be the actual name of the report). For example, if you use your report name could you have them saved to a name such as

C:\rpt1Cases.pdf
C:\rpt2Cases.pdf
C:\rpt3Cases.pdf

Otherwise, you could copy the files to that kind of naming convention (via code) when you generate your source pdf output files, or perhaps you could put the names in a table, create a recordset and refer to them that way.

 

[primagic]

The code I have the exports the lists is:

If Me.chkReport1 = True Then
DoCmd.OutputTo acOutputReport, "rpt1CasesByWHContact", acFormatPDF, "C:\CasesByWHContact.pdf", True
 
   
End If
        If Me.chkReport2 = True Then
        DoCmd.OutputTo acOutputReport, "rpt2CasesByClientContact", acFormatPDF, "C:\CasesByClient.pdf", True
      
        End If

So for each checkbox ticked if true will create the corresponding pdf.

I am trying to figure out how to modify your code so add more references to each of the pdf's (if created) and skip those ones that are not. I have tried renaming the top two to Report0, Report1 but comes up with error cannot insert pages.

Do I need to create 15 instances of pdfsrc?

I appreciate your help by the way

 

[sxschech]

Based on your code snippet, since you are not saving the file to the same name as your report, your saved pdf file does not include the rpt1, rpt2, etc. Can you modify your code to save the report as this in order to create a common name, that way, you shouldn't need 15 instances of pdfsrc. Since you would be combining the reports, you wouldn't necessarily need the fully spelled out name, however, if you also need to keep the individual pieces, see code further down.

If Me.chkReport1 = True Then
DoCmd.OutputTo acOutputReport, "rpt1CasesByWHContact", acFormatPDF, "C:\rpt1CasesBy.pdf", True
 
   
End If
        If Me.chkReport2 = True Then
        DoCmd.OutputTo acOutputReport, "rpt2CasesByClientContact", acFormatPDF, "C:\rpt2CasesBy.pdf", True
      
        End If

Otherwise if you need to keep the individual pieces you could try adding this to the beginning of your code where you create your pdf outputto.

Dim fso As New FileSystemObject

and add this line to your DoCmd.OutputTo statement

fso.CopyFile "C:\SourceName.pdf", "C:\NewName"

as shown below...

...
Dim fso As New FileSystemObject
...
...

If Me.chkReport1 = True Then
DoCmd.OutputTo acOutputReport, "rpt1CasesByWHContact", acFormatPDF, "C:\CasesByWHContact.pdf", True
fso.CopyFile "C:\CasesByWHContact.pdf", "C:\rpt1CasesBy" 
   
End If
        If Me.chkReport2 = True Then
        DoCmd.OutputTo acOutputReport, "rpt2CasesByClientContact", acFormatPDF, "C:\CasesByClient.pdf", True
fso.CopyFile "C:\CasesByClient.pdf", "C:\rpt2CasesBy"       
        End If

If you use the fso, you may need to set a reference to Microsoft Scripting Runtime in order to use it.

 

[primagic]

I have modified my code and it now creates Report0.pdf, Report1.pdf.... but doesnt save a merged file?

 

[primagic]

Found it I think

 

[sxschech]

OK. If not, I can try to help tomorrow as about to leave.

 

[primagic]

Hi sxschech,

Thank you for all your help. I managed to get it working. One thing though how would I close the pdf's after they have been created. If I select 15 reports I have 15 windows open. Can these be closed after exporting?

 

[sxschech]

Sorry took so long to get back to you. I had been looking for that solution too. Spent the morning again looking, hit lots of dead ends - either there were no answers to the person's posted question or said "I figured it out" and didn't post any code. Anyway, I found something that seems to work - so far. However, not to be another one that doesn't post the solution, I would rather wait to see your final working code, so I can tell you were to insert it as your code may be a bit different from my working code.

 

[primagic]

This is my final merge pdf code:

'Merge the pdfs

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:\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 < Forms!frmDashboard!CountReports
        ' 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", "MergedReports")
    
    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:\stMergeName, "Full", ""
    
    MsgBox "Merge Successful", vbInformation, "Merge Successful"

 

[sxschech]

Here it is. Locate the following two lines:

Part2Document.Close
Part2Document.Open (Replace(pdfsrc, "0", X))

Insert the code between the Close and Open as follows:

 Part2Document.Close
        
 'Close the open pdf files except for the Merged report
 FollowHyperlink Replace(pdfsrc, "0", X - 1), , True, False
 SendKeys "%{F4}", False
 DoEvents
                
 Part2Document.Open (Replace(pdfsrc, "0", X))

Hope it works for you.

Solution came from

http://www.pcreview.co.uk/forums/vba-open-pdf-print-then-close-stumped-t4002008.html

A solution that does not use sendkeys, which I couldn't figure out how to use because the code was opening the acrobat files and we don't need to open them as they are already open, came from

http://www.techrepublic.com/forum/questions/101-300186

 

[primagic]

Hi.

The code to close the pdf's is working however it keeps throwing up a security box which I need to accept for it to close. I have allowed macros and still the same. I have set warnings to false but still appearing.

Any ideas

Thanks again

 

[sxschech]

So far haven't experienced that issue. The pdf files on my end all close using the code I provided without any prompt. I'm using Access 2007 (Ver 12 SP2) and Adobe Acrobat Pro (Ver 9.0). Still not too familiar with the 2007 version so haven't played around too much with all the security settings. My macro settings are set to Enable all macros (not recommended; potentially dangerous code can run). I didn't notice any settings on the acrobat side, then again, you were running your pdf output using the Microsoft pdf tool and perhaps it handles security different from Adobe.

Prior to the code, when you had to manually process the reports, did the pdf files open after being created from access? If so, when you manually closed them, were there any prompts at that point? (either to save or for security)? Perhaps you could code the saving (if it is a result of pdf needing to be saved), alternatively, if you know the keystrokes you are using to click through the security boxes, you could try the sendkeys for that part after the sendkeys code you currently have and see if that works.

 

[primagic]

Hi sxschech.

I am having a few problems with my merge.

Here is the code I have at the moment:

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:\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 < Forms!frmDashboard!CountReports
        ' 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
        'Close the open pdf files except for the Merged report
       
       ' FollowHyperlink Replace(pdfsrc, "0", X - 1), , True, False
       ' SendKeys "%{F4}", False
       ' DoEvents
        
        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", "MergedReports")
    
    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:\stMergeName, "Full", ""
    
    MsgBox "Reports merged successfully", vbInformation, "Merge Successful"

It only seems to merge Report0 and Report1. Report0 is my table of contents which gets created and put at the start of the merged document. So I always have a Report0. However if I select report3 to go with report0 it doesn't merge. It will only merge if I select report1. I have tried editing my code so if the user selected the third tickbox (report3) it actually outputting report1 and that worked.

Any ideas?

 

[sxschech]

If your approach to changing the number worked can you utilize that to get the merge to work?

Otherwise, you can try to modify this part of the code which you can change based on the report number. You will need to uncomment the If statement also. This bit of code will allow you to change the count to make it skip over the report(s) you didn't pick. This code as it is will only skip one report, so you would need to have multiple if statements or set up some other logic to track the missing/selected reports to skip.

' 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

 

[primagic]

Hi sxschech

I managed to get it working.

I changed the code:

DoCmd.OutputTo acOutputReport, "rpt1CasesByWHContact", acFormatPDF, "C:\Report1.pdf", True

To create the report name on the fly so the first report they select will become report1.pdf, the second report they create will become report2.pdf and so on and it is working perfectly now.

Thank you for all you help in this.