UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export Multiple Reports To A Single Pdf, Any Version    
 
   
rsindle
post May 15 2020, 11:35 AM
Post#1


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


I am aware of using the outputTo command to output a report to a single pdf file.
However, we have a form where they can select multiple reports and we'd like to give them the opportunity to generate all the reports into a SINGLE PDF file.
Something to the effect of: These 3 reports will all end up in the single pdf listed below. If there was an APPEND flag or something, that would be GREAT!
As it is, later calls overwrite so we only get "rptReport3" in the pdf file.
Thoughts??? Thanks in advance.

DoCmd.OutputTo acOutputReport, "rptReport1", acFormatPDF, "C:\Temp\Junk.pdf", , , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rptReport2", acFormatPDF, "C:\Temp\Junk.pdf", , , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rptReport3", acFormatPDF, "C:\Temp\Junk.pdf", , , , acExportQualityPrint

Something like this would be perfect!

DoCmd.OutputTo acOutputReport, "rptReport1", acFormatPDF, "C:\Temp\Junk.pdf", , , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rptReport2", acFormatPDF, "C:\Temp\Junk.pdf", , , , acExportQualityPrint, "append"
DoCmd.OutputTo acOutputReport, "rptReport3", acFormatPDF, "C:\Temp\Junk.pdf", , , , acExportQualityPrint, "append"

Go to the top of the page
 
theDBguy
post May 15 2020, 11:41 AM
Post#2


UA Moderator
Posts: 78,446
Joined: 19-June 07
From: SunnySandyEggo


Hi Rob. Sorry, the only way I know how it to use the Acrobat API with the full version of Adobe Acrobat. Will that work for you?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rsindle
post May 15 2020, 11:50 AM
Post#3


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


Is it painful?? pullhair.gif
Also, would all the client machines need the full Acrobat?
I think that would be a show-stopper.
R-
Go to the top of the page
 
theDBguy
post May 15 2020, 11:52 AM
Post#4


UA Moderator
Posts: 78,446
Joined: 19-June 07
From: SunnySandyEggo


Yes, since the code uses the Acrobat API, all machines that's going to execute it will need the full version of Acrobat installed. Even if you use a third-party PDF creator, you would still have to install it on all machines. So, if you don't have access to Adobe Acrobat, maybe look into PDFtk. I just don't think Access can do it alone. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
DanielPineault
post May 15 2020, 12:34 PM
Post#5


UtterAccess VIP
Posts: 7,375
Joined: 30-June 11



There are a couple utilities that you can use for this. I believe that Lebans even had code dating back 10-15+ years that did this burried in his report2PDF sample. I've seen other people recommend PDFSam for basic operations like merging/splitting. I've also seen PDFtk mentioned in several discussions.

--------------------
Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
JHolm
post May 15 2020, 01:07 PM
Post#6



Posts: 147
Joined: 7-July 15
From: BC Canada


I have used a utility from Bullzip to merge existing pdf's together from Access. It can be found here. It does require a small exe to go on each clients computer, but it doesn't require an actual installation.

It's pretty simple to use, although I suspect that if you go with a utility like this, you'll need to output each selected report and then merge them together.
Go to the top of the page
 
rsindle
post May 15 2020, 03:11 PM
Post#7


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


Won't be able to use any third party software or DLL's.

However, I have another related question.
Is there a way to pass Whereclause conditions into a PDF report being done with the OutputTo command?

The following line is how we normally print the report (to the screen) and then we let them use a custom ribbon to send it to a PDF file or the printer.

DoCmd.OpenReport "rptApplication", acViewPreview, , "EnrolleeId = " & Forms!frmApplication.EnrolleeId

Is there a way to pass in the where-clause??
I know I can open the report and do it in the reports OPEN event and change the recordsource but was looking for something easy that we wouldn't have to modify the report's code.

DoCmd.OutputTo acOutputReport, "rptApplication", acFormatPDF, "C:\Temp\Application.pdf"

Thanks in advance,
R-

Go to the top of the page
 
theDBguy
post May 15 2020, 05:27 PM
Post#8


UA Moderator
Posts: 78,446
Joined: 19-June 07
From: SunnySandyEggo


Try opening the report with a WhereCondition first and then Output it to PDF. Does it do what you were hoping?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rsindle
post May 25 2020, 02:01 PM
Post#9


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


OK, here's where we are now.
We have a number of reports that users can print individually (about 10 of them).
So we can create a master report (main report) and include each of the 10 standalone reports as subreports on the main report and send it out to PDF.
We can put a hard "page break" between each subreport.
(If we don't put in the hard breaks, we sometimes get the next report starting half-way down the page)
This works great if they print all 10.
If they leave out some, we get blank pages from the "page breaks."

So, I was thinking of putting the 10 subreport controls on the main report.
Then assigning the subreports dynamically at runtime.
If I can do this, then the reports would print consecutively and all the "page breaks" would be at the end which would still suck.

So, I guess the question is, "Can we dynamically tell a "hard page break" NOT TO fire??
If not, then maybe I can make a "subreport" version of each standalone report that is identical to the standalone version
except that each one will have a hard page break at the bottom of their report.

However, this would be contingent on me being able to assign the report(s) to the subreport control via code on the main report.

Thoughts??
Rob

Go to the top of the page
 
theDBguy
post May 25 2020, 02:09 PM
Post#10


UA Moderator
Posts: 78,446
Joined: 19-June 07
From: SunnySandyEggo


Hi Rob. How do people "leave out" a report? Also, you could look into setting the Visible property of the PageBreak in the Format/Print event. I think hiding it means it won't cause a new page.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post May 25 2020, 02:15 PM
Post#11


UtterAccess VIP
Posts: 12,376
Joined: 10-February 04
From: South Charleston, WV


What is the relationship between the various reports? Why are they printed at the same time? Why do they sometimes omit some of the reports?

--------------------
Robert Crouser
Go to the top of the page
 
rsindle
post May 25 2020, 02:43 PM
Post#12


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


They are all related to whatever person they have selected to print.
However, different "default" reports would be "selected for them" depending on the various attributes of the person they have chosen to print about.
They can also choose to print other reports or to turn off some reports.
We were printing them all individually to the screen or all individually to the printer.
Now they want all to a single pdf.
If they want individual hardcopy or pdf, they'd go to the ribbon and select "print" (to printer), or "print to pdf"
If they decide to print a bunch, that's a lot of clicking around.


Go to the top of the page
 
AlbertKallal
post May 25 2020, 03:49 PM
Post#13


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


Hum, if you find a way to hide/not show given reports?

Then maybe you put the page break at the end of each of those reports. Thus, if you hide/not display one, then the page break should not occur – this is just a thought

And I also had about 3 “monster” year-end reports.

They wanted all 3-4 to appear in “one” final pdf.

So, I dropped all 3 into one new report as sub-reports. It worked rather well, and eliminated the need for some 3rd party pdf merge.

You can merge with a number of utilities, but as you noted, some restrictions exist in having to install additional 3rd party software.

If I recall correctly, if the sub-reports don’t have any data (or are filtered to not show any data, then the report does not render, and nor would a page break at the end of such reports.

So, I am just suggesting, but you might be able to move the page break to the (end of) the sub reports – and thus if they don’t show, then in theory the page breaks would not come into play.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
rsindle
post May 25 2020, 04:39 PM
Post#14


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


Albert, Thanks for looking at this.
I have already (in the past 2 hours) tried the "hard page break" at the bottom of each (sub)report.
I assumed it should be in the report footer section but it did not seem to work.
This, to me seems to have the most promise.
I have tried the following:

1. Put the subreport controls on a main report with NO page breaks. Then add a page break control at the bottom of each (sub)report at the bottom of the report footer section.
2. I tried to put in a “print vbFormFeed” command on the main form, in the EXIT event of each subreport control. It never fires because we never “enter or Exit” from the subreport controls.
3. I tried to put in a “print vbFormFeed” command in the Print event of each subreport footer.
4. I put 4 ‘empty’ report controls on the main report. Then in the open event of the main report, I evaluated what the source objects should be for the subreports.
I haven’t been able to get the open event to fire! The weird thing is if I put a msgbox "OPEN" statement in the OPEN EVENT, I see the OPEN msgbox but the very next line
of code where I try to assign reports to the SourceObject of the subreport controls NEVER fires and it doesn't ERROR out either.
UGH! Making me crazy.

Thanks for any help.
R-
Go to the top of the page
 
AlbertKallal
post May 25 2020, 05:38 PM
Post#15


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


The sub-reports open events fire even before the main form is rendered.

So, it best to start out with the sub-report(s) source object controls blank and set them as required.

When you set the source object, then quite sure the sub-reports open events fire. The issue/probem is this seqnence is hard to control because sub-report open events fire "long" before the main reports open events. (so that's why I am suggesting to leave the source object(s) blank for each sub-report, and then in your on open (or maybe on-load) event in the report "set" the sourceobject.

R
Albert
Go to the top of the page
 
projecttoday
post May 25 2020, 05:40 PM
Post#16


UtterAccess VIP
Posts: 12,376
Joined: 10-February 04
From: South Charleston, WV


If this is essential and you cannot get an acceptable result in Access your organization might want to consider using a programming language that would allow you to program it how you want.

--------------------
Robert Crouser
Go to the top of the page
 
AlbertKallal
post May 26 2020, 01:00 PM
Post#17


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


I think the "hold out" here is not really that you can't achieve this goal with Access, but that of adopting some 3rd party tools that allow PDF merging.

I would think regardless of the platform, some kind of PDF library is the solution here, and such a library would solve this issue, and "would" have to adopted by using near any development platform anyway. In other words it would be the "act" of adopting a library that solves this issue, and not that of switching away from Access to product x, y, or z. The holdout here is being allowed to adopt that 3rd party library - a requirement that quite much exists regardless of using Access or something else.

Ghostscript, and several other "free" libraries exist that would allow one with relative ease to "merge" the 3 PDF's into one, and this approach quite much would apply to most development platforms.

So I can't say that tossing out one platform for another going to result in a solution unless one is "free" or "allowed" adopt some form of 3rd party libraries or tools.

We are kind of chopping away at the wood block here, and the user "might" be able to solve this without a external PDF library, and not even requiring to have a PDF printer installed. So, all in all this "feature" of Access (creating PDF's without a printer driver, or 3rd party software is actually a "bonus" feature compared to most other solutions.

In fact, the user does have something working (ie: one pdf). The only issue is that the merge is somewhat of a kluge (3 sub-reports in one). So, a solution would be a lot easier if some kind of additional library was able to be adopted and used here. However, the fact that you can combine multiple reports into one with such great ease in Access, and even dropping in sub-reports works for PDF's? Rather a amazing ability we have in Access.

I think this comes down does one fiddle more with the sub-report trick, or adopt a library to do this? The issue then becomes the install of that software.

My approach? I would create a .net com object that includes a pdf library, and I would use my .net "side-loader". The result is the addition of 2-3 .dll's in the same folder as the access FE, but no installing or registering of the .dll's is/would be required. I adopted .net side loading in Access since I am finding so many corporate environments don't allow "elevated" rights which are typical required for 3rd party software. This side-loading approach thus allows me to by-passes this issue for locked down environments.

However, to be fair? Note how I am much agreeing that a different platform does open up more doors, but I would still be adopting a 3rd party library - but with my side-loading approach? Zero installing and registering of that PDF library would be the result.

So I would simply include these .dll's and NOT make waves. After all, the policy is not installing 3rd party software, but that does not mean adding a few extra .dll's to my access application that does not require any install!

To the OP:
If you want, I will attach + post a working .dll solution that requires no install, and would allow merging of the 3 pdfs. This would add 2-3 dll's to your deployment, but it would be a "x-copy" deploy just like a typical FE update is.

R
Albert
Go to the top of the page
 
rsindle
post May 26 2020, 02:50 PM
Post#18


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


Albert,
That would be awesome! Would love to try that out.

In the meantime, we came to a hybrid solution for the pdfs.
We have 3 options, Print to screen, print to printer, print to pdfs.

The first 2 print exactly what they've asked for.

The PDF print will always print the 6 "required" reports, and the other 4 (rarely selected) optional reports will pdf individually.
Since the required reports are always 1 or 2 page reports (without Memo fields and such that would make them unpredictable lengths) we did the following:
For the 1 page reports, we moved any PAGE Header/Footer controls and codes into the REPORT Header/Footer sections.
The couple of 2-page reports, we had to move PAGE Header/Footer controls and code into the DETAILS sections and with a bit of trial and error everything printed great.

The only real pain is that since we are using the same reports for each of the 3 options, and they were designed to Print individually (not as subreports),
the PAGE Header/Footer controls and code had to be moved, since they don't Exist or Fire when in subreport controls.

So all good for now, but would love to try out the PDF combiner DLL's. Just let me know "where" to copy them for the pseudo-install.
smile.gif

Thanks all,
Rob



Go to the top of the page
 
AlbertKallal
post May 29 2020, 04:12 AM
Post#19


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ok, lets try what I suggested.

A few things:

I should add a few more "helper" functions to the pdf merge I am sharing here. This is based on the .net pdf iSharp library.
It has TONS of cool features - like display a pdf, get pages, get (extract) text, and gazillion other features.

But, for this, we shall use the MOST bare bones, the MOST simple, and the MOST easy!

So, I attached a working sample.

The ONLY requirement is you place the 3 "small" .dll's in the SAME folder as your front end.

Now, if you want to try "early" binding, you can "register" the .dll (this is just like we much register any other .dll that we often had to do in the past). But, these days, registering is PAIN, and with this approach you do not.

And you could register, and use early binding, that way you get nice inteli-sense in VBA.

So, here is a code snip - early binding. (you having to register the .dll's

CODE
Sub TestMerge()

   ' early binding example

   Dim MyPdf      As New Pmerge.Pmerge

   MyPdf.Add "c:\test4\p1.pdf"
   MyPdf.Add "c:\test4\p2.pdf"

   MyPdf.OutPutDoc = "c:\test4\P1P2.pdf"

   MyPdf.Merge
  
  
End Sub


Nice, easy - could not be more simple.

However, the above code sample requires that you have to "register" the .dll.

So, now lets do this with the .net loader I have.

I will for the sake of history do a FINE AND HUGE thank you to the Great Access Wizard Stephan Lebans.

I learned the existence of the "load library" trick from him in about 2002 - 18 years ago. And to keep this post short?
LoadLibrary was often used by VB6 folks. I should point out that this trick is NOT for "com/ActiveX" .dll's, but for standard windows .dll's.
(those we use declare "lib" path name to some .dll.

LoadLibrary win32 api is used to eliminate the need to hard code the path to the .dll.

So, I ASSUME that the .dll's will be in the same folder as the FE.

There are 3 .dll's included here.

nloader.dll - this is windows x32 bit .dll - it will load any .net dll for use.
(thus, any .net .dll written as a COM object can be used by this routine. If you are writing .net code, then this is a "gem stone").

Pmerge.dll - this is the .net wee bit of code I JUST wrote for this example.

Pmerge however uses the .net iSharpPDF library.

So to make this work, you simply place my "universal" .net load (nloader.dll), the code I wrote in .net for this example (Pmerge.dll) and then a dependency .(PdfSharp.dll).

Short Story: just place the 3 .dll's in the same folder as the FE - it will just work!!

So, the above code as late binding, a it "just works", and not having to do ANYTHING but just run the code?

The above code becomes this:

CODE
Sub TestMerge2()

    ' late binding, using side loader
    
    Dim MyPdf     As Object
    Set MyPdf = CreateObjectNET("Pmerge.dll", "Pmerge.Pmerge")
    
    
   MyPdf.Add "c:\test4\p1.pdf"
   MyPdf.Add "c:\test4\p2.pdf"
  
   MyPdf.OutPutDoc = "c:\test4\P1AP2.pdf"
  
   MyPdf.Merge
    
End Sub


So the code is really the the same as the first example, but of course uses late binding.

I have attached the above working code. Just un-zip to a folder - and run it. Of course change the file names used.

And for those that are wondering? What does the .net code look like?

it is short - and is simply a vb.net class - written as a exportable standard windows "COM" object.

CODE
Imports PdfSharp.Pdf
Imports PdfSharp.Pdf.IO

Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual)> Public Class Pmerge

    Private DocList As New List(Of String)
    Public OutPutDoc As String

    Public Sub Add(strDoc As String)

        docList.Add(strDoc)

    End Sub

    Public Sub Merge()

        MergePDFs(OutPutDoc, docList)

    End Sub

    Public Sub Clear()
        DocList = New List(Of String)
        OutPutDoc = ""
    End Sub

    Public Function PdfFiles() As System.Collections.IList

        Return DocList

    End Function

    Private Sub MergePDFs(ByVal targetPath As String, pdfs As List(Of String))
        Using targetDoc As PdfDocument = New PdfDocument()

            For Each pdf As String In pdfs

                Using pdfDoc As PdfDocument = PdfReader.Open(pdf, PdfDocumentOpenMode.Import)

                    For i As Integer = 0 To pdfDoc.PageCount - 1
                        targetDoc.AddPage(pdfDoc.Pages(i))
                    Next
                End Using
            Next

            targetDoc.Save(targetPath)
        End Using
    End Sub


End Class


I included the .net code to show how little code I had to write here.

You can give the above a try. Now I could/should add a few more functions to above. As noted, the pdf iSharp library has a truckload of "cool" features. You can for example "extract" text from a given pdf.

One more REALLY great aspect?

The size of the above compiled code example is a crazy small .dll - it is 9KB in size!!! This is a wonderful aspect of .net code - the resulting compile size for a .dll are oh so just small and tiny.

Now the PdfSharp.dll is a incredible feature rich library. It is only 523KB in size. So the total increase here is small - and the size of your deploy really not going to be noticed.

Attached:
A working Access (2010) sample. It is ONLY the above code - bare bones as possible.

So, un-zip to any folder. Edit the VBA to add legal path names to some pdfs, and give it a try.

Note that I only merged 2 files. But you can merge 2 or 20 files - just use the .Add "file name" over and over to add more files for the merge.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Attached File(s)
Attached File  Pmerge.zip ( 243.27K )Number of downloads: 7
 
Go to the top of the page
 
rsindle
post May 29 2020, 08:05 AM
Post#20


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


Wow! Very cool.
I won't get to it until next week, but thanks a ton!
Will give it a try!
Really appreciate your time and expertise.
Thanks Abert,
notworthy.gif
Rob
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th July 2020 - 04:40 AM