Multiple reports to one PDF (1 Viewer)

Hi all,

I am literally near tearing my hair out! My problem is this;

I have 4 access reports to upload, I have used this code previous to save each one individually as PDF's:

Dim fileName As String, fldrPath As String, filePath As String
fileName = "FinancialReport.pdf" 'filename for PDF file*
fldrPath = "C:\Users\cmcbrien\Documents\PDF Exports" & Me.JobNumber & "_" 'folder path where pdf file will be saved *"
filePath = fldrPath & "" & fileName & ".pdf"
DoCmd.OpenReport "rptFinancialReport", acViewPreview ', , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, fldrPath & fileName, True
DoCmd.Close acReport, "rptFinancialReport"

Dim fileName_SlidingScale As String, fldrPath_SlidingScale As String, filePath_SlidingScale As String
fileName_SlidingScale = "SlidingScale.pdf" 'filename for PDF file*
fldrPath_SlidingScale = "C:\Users\cmcbrien\Documents\PDF Exports" & Me.JobNumber & "_" 'folder path where pdf file will be saved *
filePath_SlidingScale = fldrPath_SlidingScale & "" & fileName_SlidingScale & ".pdf"
DoCmd.OpenReport "rptCalculatedJobBonusScheme", acViewPreview ', , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, fldrPath_SlidingScale & fileName_SlidingScale, True
'Let's close our previewed report
DoCmd.Close acReport, "rptCalculatedJobBonusScheme"

Dim fileName_AllocatedBonuses As String, fldrPath_AllocatedBonuses As String, filePath_AllocatedBonuses As String
fileName_AllocatedBonuses = "AllocatedBonuses.pdf" 'filename for PDF file*
fldrPath_AllocatedBonuses = "C:\Users\cmcbrien\Documents\PDF Exports" & Me.JobNumber & "_" 'folder path where pdf file will be saved *
filePath_AllocatedBonuses = fldrPath_AllocatedBonuses & "" & fileName_AllocatedBonuses & ".pdf"
DoCmd.OpenReport "rptCalculatedBonuses", acViewPreview ', , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, fldrPath_AllocatedBonuses & fileName_AllocatedBonuses, True
DoCmd.Close acReport, "rptCalculatedBonuses"

Dim fileName_ReleasedBonuses As String, fldrPath_ReleasedBonuses As String, filePath_ReleasedBonuses As String
fileName_ReleasedBonuses = "ReleasedBonuses.pdf" 'filename for PDF file*
fldrPath_ReleasedBonuses = "C:\Users\cmcbrien\Documents\PDF Exports" & Me.JobNumber & "_" 'folder path where pdf file will be saved *
filePath_ReleasedBonuses = fldrPath_ReleasedBonuses & "" & fileName_ReleasedBonuses & ".pdf"
DoCmd.OpenReport "rptReleasedBonuses", acViewPreview, , "[JobID]=" & Me.JobID ', , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, fldrPath_ReleasedBonuses & fileName_ReleasedBonuses, True
DoCmd.Close acReport, "rptReleasedBonuses", acSaveNo


I am wondering can I save all to one PDF and make the process much quicker (I have 100 jobs to go through). I have tried making one master report and including all reports in the master report as subforms. However, the issue with this is that two of the reports are horizontal, whereas the other two are vertical. I am sure this can be done. This is the other code I am looking at to use in vba, but I am unsure as to how it works;

Private Sub Command2186_Click()
' ZVI:2013-08-27 http://www.vbaexpress.com/forum/sho...e-PDF-files-in-a-folder-using-adobe-acrobat-X
' Reference required: "VBE - Tools - References - Acrobat"

' --> Settings, change to suit
Const MyPath = "C:\Temp" ' Path where PDF files are stored
Const MyFiles = "1.pdf,2.pdf,3.pdf" ' List of PDFs to ne merged
Const DestFile = "MergedFile.pdf" ' The name of the merged file
' <-- End of settings

Dim A As Variant, i As Long, n As Long, ni As Long, p As String
Dim ACroApp As New Acrobat.ACroApp, PartDocs() As Acrobat.CAcroPDDoc

If Right(MyPath, 1) = "" Then p = MyPath Else p = MyPath & ""
A = Split(MyFiles, ",")
ReDim PartDocs(0 To UBound(A))

On Error GoTo exit_
If Len(Dir(p & DestFile)) Then Kill p & DestFile
For i = 0 To UBound(A)
' Check PDF file presence
If Dir(p & Trim(A(i))) = "" Then
MsgBox "File not found" & vbLf & p & A(i), vbExclamation, "Canceled"
Exit For
End If
' Open PDF document
Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
PartDocs(i).Open p & Trim(A(i))
If i Then
' Merge PDF to PartDocs(0) document
ni = PartDocs(i).GetNumPages()
If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
MsgBox "Cannot insert pages of" & vbLf & p & A(i), vbExclamation, "Canceled"
End If
' Calc the number of pages in the merged document
n = n + ni
' Release the memory
PartDocs(i).Close
Set PartDocs(i) = Nothing
Else
' Calc the number of pages in PartDocs(0) document
n = PartDocs(0).GetNumPages()
End If
Next

If i > UBound(A) Then
' Save the merged document to DestFile
If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
MsgBox "Cannot save the resulting document" & vbLf & p & DestFile, vbExclamation, "Canceled"
End If
End If

exit_:

' Inform about error/success
If Err Then
MsgBox Err.Description, vbCritical, "Error #" & Err.Number
ElseIf i > UBound(A) Then
MsgBox "The resulting file is created:" & vbLf & p & DestFile, vbInformation, "Done"
End If

' Release the memory
If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
Set PartDocs(0) = Nothing

' Quit Acrobat application
ACroApp.Exit
Set ACroApp = Nothing

End Sub

By the way, each time I use this code I get user-defined type not enabled and the debugger stops at the 'Dim ACroApp As New Acrobat.ACroApp' line. I got the code online, but I have not figured out how it merges two pdf's to one.

I am certain there is a way to do this? Any advice would be greatly appreciated.

Also, how do you download the 10.0 type library from Adobe Acrobat. I downloaded Acrobat Reader but only the 3.0 Type Library was available?

Thanks for all your help in advance, this has been a long day searching for the solution to this!!

Best,
Michael

Can you put all the reports as subReports in 1 master report,then print to PDF?

I searched "Use VBA to combine PDF Files" on Google and found this:
https://stackoverflow.com/questions/29504053/open-and-merge-multiple-pdf-files-using-vba

Which led to this:
https://groups.google.com/forum/#!topic/adobe.acrobat.windows/GC2vVVPvUOk

It appears that the second link has what you need. (Maybe not exactly but the methods look like they can be adapted to what you need.)

In my experience (I have code for combining PDF files) the library is already available but requires Adobe Acrobat to be installed, not just Adobe Reader.

Michael,

In the future, please use the code tag "#" to put your code into something more readable. It also clearly lets others know what is or isn't code and lets you indent.

PDFTools will combine pdfs and a whole lot of other pdf related tasks including rotate.

Your repetitive code should be implemented as a function or sub called in a loop.

Wow,

guys thanks a million for the responses!!! Yes @Mark_ I will code indent in future. Apologies!!

And @Ranman256 I tried to use all in the one report but because some of my reports are portrait and some are landscape, it does not format correctly.

And @Galaxiom, yes I will maybe place the repetive code in a loop to optimize it now.

I sincerely appreciate all the advice. I will get to work on that now @Insane_ai !! And let you know how I get on.

Best,
Michael

i have used pdftk to combine pdfs into 1.
you can download pdftk and there is a demo excel with it.
you can combine those individual pdf into 1 pdf using a .bat file.
by the way it is freeware and run on command line.