Combining Multiple Reports in PDF From Access

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Combining Multiple Reports in PDF From Access

Post by Leesha »

Hi,
I have a user who runs 3 different reports / store when the invoice is generated. This is not a problem when individual invoices are being run however it is cumbersome when 600+ are being generated at one time. Currently, they generate the invoices, then print an Access report [rptInvoice]that contains all 600 invoices. Then they print two other reports [rptSalesAdjustments] and [rptRentCalculator] for the 600 invoices. The final step is coalating the three reports together for each store. I would like to give them the ability to print 1 report that contains the three reports listed above, coalated by store. I've tried doing it using subreports but the pages don't work out the way they do in individual reports. Is something like this possible?

Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combining Multiple Reports in PDF From Access

Post by HansV »

If you have Adobe Acrobat, you can set a reference to its object library in Tools > References..., then use code as described in Merge 3 PDF files together into 1 PDF file using VBA.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

Hi Hans,
I have this on my computer but I don't believe they have the full version and if they do it's not on all computers. Would they have to have the full version or just the Adobe reader?
Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combining Multiple Reports in PDF From Access

Post by HansV »

It won't work if they only have Adobe Reader, the full version of Adobe Acrobat is required to combine multiple PDF files into one.

As an alternative, you could download and install the utility PDFtk - The PDF Toolkit. It comes with a command-line utility PDFtk Server. All users would need PDFtk Server too, and "A commercial license is required to distribute PDFtk with your commercial product."
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

Thanks Hans! I will look into this.
Leesha

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

Hi Hans,

Back to this one again. The group installed Adobe Acrobat on their computers which is cool. So now my question is do I put a check into all of the Adobe references in the library or just the one that says "Adobe Access 3.0 Type Library". Also, the code you referenced in the link above, what do I replace with the names of my reports? And the 3 references to "chk" - I'm not sure what those are.
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combining Multiple Reports in PDF From Access

Post by HansV »

If you use the following version, you don't need to set any references, nor do you need the variables chk1 etc.

Code: Select all

    Dim gPDDoc1 As Object
    Dim gPDDoc2 As Object
    Dim gPDDOC3 As Object
    Set gPDDoc1 = CreateObject("AcroExch.PDDoc")
    Set gPDDoc2 = CreateObject("AcroExch.PDDoc")
    Set gPDDOC3 = CreateObject("AcroExch.PDDoc")
    gPDDoc1.Open "C:\test\test1.pdf" ' path of first source file
    gPDDoc2.Open "C:\test\test2.pdf" ' path of second source file
    gPDDOC3.Open "C:\test\test3.pdf" ' path of third source file
    gPDDoc1.InsertPages 0, gPDDoc2, 0, 1, 0
    gPDDoc1.InsertPages 1, gPDDOC3, 0, 1, 0
    gPDDoc1.Save 1, "C:\test\merged.pdf" ' path of destination file
You must replace the paths and filenames of the source files and of the destination file with the ones you need.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

Hi Hans,
Thanks for the info above. If I am reading this correctly it looks as if each access report is already generated as a pdf and saved as three individual pdfs. The process right now is that there are three different Access reports that are generated separately. Each report contains data for 500 or more stores. When the reports run, they are printed and then hand collated. The formatting doesn't work using subreports in one report which is why there are three different reports. My goal/thought was to combine the three reports into one pdf and then they could print them already collated for the 500+ stores. I'm not sure that the above code is going to accomplish this unless I'm reading it incorrectly.
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combining Multiple Reports in PDF From Access

Post by HansV »

If you want to print reports physically, you can use the technique described in How to use Visual Basic code to collate and to print two reports. It can easily be modified for three reports.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

OK, will take a shot at this over the weekend. Will this allow me to have each of the three reports print for each store, then loop onto the next store until they are done?
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combining Multiple Reports in PDF From Access

Post by HansV »

You can loop through the stores, open the first report with a wherecondition to display the data for the current store only, and print it, then repeat for the second and third reports. So you'd print:

Report 1 for store 1
Report 2 for store 1
Report 3 for store 1
Report 1 for store 2
Report 2 for store 2
Report 3 for store 2
...
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

OK, and I would just set it to print vs preview correct??

Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combining Multiple Reports in PDF From Access

Post by HansV »

Yes, indeed.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

Cool! Can't wait to work on it!

Thanks,
Leesha

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

Hi,
I revised the code you referred me to include three reports. They run OK but I get this error at the end and am not sure what it is referring to:
“Object doesn’t support this property or method”
This is the code in the module:
Function CollateReports(NumPages, Rpt1 As String, Rpt2 As String, Rpt3 As String)
Dim MyPageNum As Integer
'Set the page number loop and alternate printing the report pages.
For MyPageNum = 1 To NumPages
'NumPages is the number of pages to print.
DoCmd.SelectObject acReport, Rpt1, True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
DoCmd.SelectObject acReport, Rpt2, True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
DoCmd.SelectObject acReport, Rpt3, True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
Next MyPageNum
End Function
This is the code to print the reports. I haven’t tried to get it to look through all of the stores yet.
Print CollateReports(1, "rptInvoiceCSVAllImport", "rptSalesAdjustmentReportAutoImport2", "rptRentCalculatorReportAutoImport2")

Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combining Multiple Reports in PDF From Access

Post by HansV »

Which line causes the error?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

The code behind the command button:
Print CollateReports(1, "rptInvoiceCSVAllImport", "rptSalesAdjustmentReportAutoImport2", "rptRentCalculatorReportAutoImport2")

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combining Multiple Reports in PDF From Access

Post by HansV »

That is not a valid instruction. Try this first:

Call CollateReports(1, "rptInvoiceCSVAllImport", "rptSalesAdjustmentReportAutoImport2", "rptRentCalculatorReportAutoImport2")

This should print the first page of each of the reports. If that works, try

Call CollateReports(2, "rptInvoiceCSVAllImport", "rptSalesAdjustmentReportAutoImport2", "rptRentCalculatorReportAutoImport2")

This should print the first page of each report, then the second page of each report. Eventually, use the numbe of stores. (This assumes that the report for each store will be one page)
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Combining Multiple Reports in PDF From Access

Post by Leesha »

Thanks Hans! That worked! Of course I have more questions/need more help. :-( I will start with the first item. I noticed that when the reports printed, the WalmartNumber didn't correspond for all three reports. This is becasue for one of the reports, there was no info for that store. How would I keep the report from printing if there there is no data for that particualar report. Since I will eventually need to printing to loop through the records in [tblImporttemp] it is important that the three reports correspond to the same WalmartNumber.

Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combining Multiple Reports in PDF From Access

Post by HansV »

That complicates it. I think we'll need a different approach to solve this problem.

I don't have time right now to look into it, I'll get back to you later today.
Best wishes,
Hans