Combining Multiple Reports in PDF From Access
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Combining Multiple Reports in PDF From Access
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
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
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
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."
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
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
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
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
If you use the following version, you don't need to set any references, nor do you need the variables chk1 etc.
You must replace the paths and filenames of the source files and of the destination file with the ones you need.
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
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
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
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
Leesha
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
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
...
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
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
OK, and I would just set it to print vs preview correct??
Thanks!
Leesha
Thanks!
Leesha
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
Cool! Can't wait to work on it!
Thanks,
Leesha
Thanks,
Leesha
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
The code behind the command button:
Print CollateReports(1, "rptInvoiceCSVAllImport", "rptSalesAdjustmentReportAutoImport2", "rptRentCalculatorReportAutoImport2")
Print CollateReports(1, "rptInvoiceCSVAllImport", "rptSalesAdjustmentReportAutoImport2", "rptRentCalculatorReportAutoImport2")
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
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)
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
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
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
Thanks,
Leesha
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
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.
I don't have time right now to look into it, I'll get back to you later today.
Best wishes,
Hans
Hans