How merge three reports into one pdf file

I have a database with three reports that I want to merge into one pdf file.  How can I do this via VBA code?

--Steve
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
Sorry, I meant 4 reports, not 3.
Jeffrey CoachmanMIS LiasonCommented:
Well you cant do this in Access VBA, ...if this is your question...
You have to export the reports to PDF,...Then:
1. Use one of the many "combine PDF" programs out there to combine them.
Or
2. Buy the full version of Adobe and do the combination there.

The downside here is that the page numbering will not "automatically adjust", to print accurate page numbers for a "combined" PDF Report.

JeffCoachman

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could also create a master report and add the 4 as subreports and then use OutputTo in VBA to create the PDF.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SteveL13Author Commented:
I tried to create a mew master report and put the reports in subreports but some of the report sections don't appear.  ???
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
subreports won't show headers and footers, but you can simulate one by adding a group and putting the info there. Just create it with a fake value like "=1" or something like that.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Steve,

There is nt a built in Way in Office to merge together PDF files.

The good news is there is a free tool that can merge PDF files together. I have been using it with Access form many years now. Once you import eh modules, you only have to add a few lines of code to your application.

Check out: http://www.lebans.com/reporttopdf.htm

I have use it successfully with Access 2000,2002,2003,2007,2007,2010, and 2013.
SteveL13Author Commented:
Boyd,

I don't have a clue as to how to use it.
Jeffrey CoachmanMIS LiasonCommented:
The instructions are in the code.

That sample is popular with versions of Access prior to 2007 (that had no direct PDF support)
I had not noticed that it could now merge PDF's
It worked fine for me, the very first time it tried it.
Took about a minute to read/modify the code

Examine the sample fully, I am sure with your experience here with VBA, it will be simpler than you thought...

Jeff
SteveL13Author Commented:
I imported these objects from the downloaded mdb file:

clsCommonDialog
modDocumentSource
modReportToPDF
HowToUse
READMEFIRST

Right so far?

But then I don't know where to put the names of the reports I want to combine into one report.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
SteveL13,

Check out the demo form. I looked at it enough to see how  all the routines were called.

Here is how to merge two PDFs.

The file I want first I copy to a new file (pMasterPDF)

To append a file to this new file I use:
 MergePDFDocuments(pMasterPDF, pFileToAppend)

Open in new window


Repeat until you have all the PDFs appended.

I usually use some type of looping routing to process all the files.


Note:
I have never actually looked at all the code in the modules to see how it works. I do not need  to or care to know how MergePDFDocuments() actually works. I only need to know how to call the routine.



Here is just a possible example of how to create the PDF with 2007/201/2013

In the On Click of a command button use:

Example Steps:
1) save first report to a PDF

DoCmd.OutputTo acOutputReport,"Report1", acFormatPDF,"c:reports\report1.pdf"

2) save second report to a PDF

DoCmd.OutputTo acOutputReport,"Report2", acFormatPDF,"c:reports\report2.pdf"


3) Merge the second PDF with the first PDF:

MergePDFDocuments("c:\reports\report1.pdf", "c"\reports\report2.pdf")

4) save third report to a PDF

DoCmd.OutputTo acOutputReport,"Report3", acFormatPDF,"c:reports\report3.pdf"

5) Merge the third PDF with the first PDF:

 MergePDFDocuments("c:\reports\report1.pdf", "c"\reports\report3.pdf")

6) save fourth report to a PDF

DoCmd.OutputTo acOutputReport,"Report4", acFormatPDF,"c:reports\report4.pdf"

7) Merge the forth PDF with the first PDF:

  MergePDFDocuments("c:\reports\report1.pdf", "c"\reports\report4.pdf")


the result wll be that report1.PDF has the other reports appended to the end. All in a single PDF.
SteveL13Author Commented:
To all... I'm still experimenting with this.
GozrehCommented:
If you have Adobe Acrobat installed on your PC, you can reference VBA to the Acrobat Dll and use this code
Function CombinePDF(MasterPDF As String, ChildPDF As String)
   Dim objCAcroPDDocDestination As Object   ' Acrobat.CAcroPDDoc
   Dim objCAcroPDDocSource As Object   ' Acrobat.CAcroPDDoc

   Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
   Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")

   objCAcroPDDocDestination.Open MasterPDF
   objCAcroPDDocSource.Open ChildPDF
   objCAcroPDDocBlank.Close
   
   If objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
      '-1 Success
   Else
      '0 problem
   End If
   objCAcroPDDocSource.Close

   objCAcroPDDocDestination.Save 1, MasterPDF
   objCAcroPDDocDestination.Close
   Set objCAcroPDDocSource = Nothing
   Set objCAcroPDDocDestination = Nothing

End Function

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.