×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Merge reports to single pdf
2

Merge reports to single pdf

Merge reports to single pdf

(OP)
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
 

RE: Merge reports to single pdf

2
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

    

CODE

Sub MergePDF()
    'Combined multiple PDF files into one
    'set a reference to Acrobat (Adobe Acrobat 7.0 Type Library)
    'http://www.khk.net/wordpress/2009/03/04/adobe-acrobat-and-vba-an-introduction/
    '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

RE: Merge reports to single pdf

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

Code below is:

CODE

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


 

RE: Merge reports to single pdf

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 ="".

CODE

If Dir(stPath & stFileName) = "" Then

RE: Merge reports to single pdf

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

I can only see one.

CODE

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

RE: Merge reports to single pdf

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.  

RE: Merge reports to single pdf

(OP)
The code I have the exports the lists is:

CODE

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

RE: Merge reports to single pdf

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.

CODE

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...

CODE

...
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.

RE: Merge reports to single pdf

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

RE: Merge reports to single pdf

(OP)
Found it I think

RE: Merge reports to single pdf

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

RE: Merge reports to single pdf

(OP)
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?

RE: Merge reports to single pdf

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.

 

RE: Merge reports to single pdf

(OP)
This is my final merge pdf code:

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"

RE: Merge reports to single pdf

Here it is. Locate the following two lines:

CODE

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

Insert the code between the Close and Open as follows:

CODE


 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
 

RE: Merge reports to single pdf

(OP)
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

RE: Merge reports to single pdf

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.

RE: Merge reports to single pdf

(OP)
Hi sxschech.

I am having a few problems with my merge.

Here is the code I have at the moment:

CODE

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?

RE: Merge reports to single pdf

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.

CODE

' 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

 

RE: Merge reports to single pdf

(OP)
Hi sxschech

I managed to get it working.

I changed the code:

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.

RE: Merge reports to single pdf

Glad you got it working.

RE: Merge reports to single pdf

(OP)
Hi again,

I have your code working perfect. Was just wondering is there anyway of creating a hyperlink from a table of contents page to the first page of each report?

My report0 is always a table of contents. This just lists the Report names. I was wondering if I could turn that into a hyperlink and link to the first page of each report in the merged document?


Is this possible?

RE: Merge reports to single pdf

Thank you TheAceMan1.  That was nice of you.  Your advice has been helpful over the years, and am glad was able to provide something that is useful to others.

RE: Merge reports to single pdf

primagic, I'm sure it could be done, would have to look into it.  It may need to be done on the acrobat side once the document is completed.

RE: Merge reports to single pdf

(OP)
yes i am sure it could be done as well. but beyond my knowledge unfortunately :)

If you could come up with a solution, i would appreciate it alot.

Many thanks.

 

RE: Merge reports to single pdf

Following up on our topic...

Recently, after a Windows and Office update, I experienced the issue of the security popup box for each of the pdf's and found out that when I save the pdfs to the hard disk (C drive) I get the message, while if I save to a network drive, everything is fine.  I spoke with our ITS dept and they tried several things in the registry settings, but haven't yet been able to fix the problem and recommended that since saving to the network doesn't cause the popups, that I change my code to save to the network rather than the C drive.

I haven't yet found a solution for the hyperlink table of contents, but did find a code solution to closing the pdf files without using sendkeys.

Go to the vba window and insert a new module.  Paste the following code I found at 'http://www.andreavb.com/forum/viewtopic_5604.html

The modification that I made was to accept the filename of the window to be closed via code rather than as an input box.

CODE

'http://www.andreavb.com/forum/viewtopic_5604.html
'Post:Asim-GDI GURU
Option Compare Database


Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Const SW_SHOWNORMAL = 1
Const WM_CLOSE = &H10

Public Sub WinClose(WindowToClose As String)
    'KPD-Team 1998
    'URL: http://www.allapi.net/
    'E-Mail: KPDTeam@Allapi.net
    Dim WinWnd As Long ', Ret As String,
    Dim RetVal As Long, lpClassName As String
    'Ask for a Window title
    'Ret = InputBox("Enter the exact window title:" + Chr$(13) + Chr$(10) + "Note: must be an exact match")
    ret = WindowToClose
    'Search the window
    WinWnd = FindWindow(vbNullString, ret)
    If WinWnd = 0 Then MsgBox "Couldn't find the window ...": Exit Sub
    'Show the window
    ShowWindow WinWnd, SW_SHOWNORMAL
    'Post a message to the window to close itself
    PostMessage WinWnd, WM_CLOSE, 0&, 0&
End Sub

Next in the MergePDF code replace this section

CODE

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

with

CODE

'Close the open pdf files except for the Merged report
DoEvents
Call WinClose(Replace(Mid(pdfsrc, InStrRev(pdfsrc, "\") + 1), "0", X - 1) & " - Adobe Acrobat Pro")
DoEvents

The WinClose is used to close other open Adobe Acrobat application windows, which is useful if you want to close a specific window without closing the entire application that may have other open windows.  You can find out the syntax by looking at the Title Bar (above the File menu) of the open Acrobat PDF file window you want to close.

Example:

WinClose("rptMyReportName.pdf - Adobe Acrobat Pro")
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close