Combining Reports into 1 (1 Viewer)

[Valentine]

Good afternoon,

I currently have a button that prints 1-n reports in PDF format. I know how to combine those PDFs into 1 using VBA if I have the exact number of reports being printed. Some weeks I will print 1 and some weeks I can print up to 6.

My question is: Can I make an array count variable?

slideCount = 2
jcrbReportArray(0) = "JCRB Cover Page 1.pdf"
jcrbReportArray(1) = "JCRB Overview 2.pdf"
I = 0

For Each JCRBReport in jcrbReportArray
     set pdfFile(I) = CreateObject(AcroExch.PDDoc")
     pdfFile(I).Open srcFolderName & JCRBReport
     If I <> 0 Then
          numPages = pdfFile(I).GetNumPages()
          If Not pdfFile(0).InsertPages(totalNumPages - 1, pdfFile(I), 0, numPages, True) Then
               MsgBox "Cannot insert pages", vbExcalamation, "Canceled"
          End If
          totalNumPages = totalNumPages + numPages
          pdfFile(I).Close
          Set pdfFile(I) = Nothing
     Else
          totalNumPages = pdfFile(0).GetNumPages()
     End if
     I = I + 1
Next JCRBReport

The above is how I am doing it when I know the number of pages to combine

 

[Ranman256]

in 1 single report that is nothing but subreports,
put in N subrpt boxes each mapped to a report.
hand assign each subrpt to a source,

--- or ----
in a table put in the names of all the reports to use.
in vb , open the report in design, cycle thru the table of reports assign each to a subRpt-N
then print.

 

[Valentine]

Neither of those worked for me.

I made a Report that reads like a Word document and when I push the button it prints that report to a folder in pdf format as well as 1 - n of my requirement reports. I want to be able to combine those pdf files into 1 using VBA. I don't know the exact number of files to combine every week as it changes. Is there a way I can do the above code but with an open ended array? or maybe a way to just add to the first pdf all files that start with a certain character in this folder.

 

[Valentine]

I found this:

Combine PDFs with VBA and Adobe Acrobat - wellsr.com

Combine PDFs with VBA using this macro. The simplicity of this tutorial illustrates how easy it is to use Adobe Acrobat to merge PDF files in Word and Excel.

wellsr.com

But again I don't know the exact number of files to be merged each week, is there a way to do this without knowing how many files?

 

[Valentine]

Switching gears slightly, I am now trying to loop through the folder and grab all the pdfs with "CNF" in the front. I want to create an array with this but don't know how to write it.

Public Function LoopThrough(strDir As String, strType As String)

Dim file as Variant

If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
file = Dir(strDir & strType)
While (file <> "")

I will call this function with:
Call LoopThrough("C:\Users\Justin\Documents\", "CNF*")

There could be 1 or there could be 10 in the folder I want to grab them all and create the array, please help.

 

[theDBguy]

Switching gears slightly, I am now trying to loop through the folder and grab all the pdfs with "CNF" in the front. I want to create an array with this but don't know how to write it.

Public Function LoopThrough(strDir As String, strType As String)

Dim file as Variant

If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
file = Dir(strDir & strType)
While (file <> "")

I will call this function with:
Call LoopThrough("C:\Users\Justin\Documents\", "CNF*")

There could be 1 or there could be 10 in the folder I want to grab them all and create the array, please help.

I don't see the need to use two loops (one to collect the files in an array and another to go through that array to create the pdfs) or the need to stick with using arrays. You should be able to use one loop with the Dir() function to get what you want.

 

[Valentine]

How do I put that into an array though without knowing the total number of files? If I go files = Dir("C:\Users\Justin\Documents\", "CNF*") How do I get them into the array to merge them?

 

[theDBguy]

How do I put that into an array though without knowing the total number of files? If I go files = Dir("C:\Users\Justin\Documents\", "CNF*") How do I get them into the array to merge them?

Seems like your mind is stuck with thinking about using an array. As I said, though, you don't have to stick with using an array. You can use the Dir() function in a loop and get the same result. But if you want to insist on using an array, check out the ReDim statement.

 

[Valentine]

I am trying the easiest route haha and I have already done the array way, but if there is a way to merge from Dir() I will definitely like to try that. Everything I have seen on merging pdfs is print them and then merge them as an array I have no idea how to do it otherwise.

 

[Valentine]

This is what I have currently and it does SOME of what I want. The Initial Report that I want to insert pages into prints properly and the CNF reports print properly, but the part of the code that tries to insert the CNF Reports into the Nominations report is not working. I do get the "Testing" save function but it just has the initial Nominations report with no CNF reports.

Dim reportName as String
Dim initialFolderLocation as string
Dim fileName as String
Dim fileNameCnf as String
Dim reportNameCnf as String
Dim strCnf as String
Dim rsCnf as DAO.Recordset
Dim dbCurr as DAO.Database
Dim cnfId as String
Dim cnfTitle as String
Dim cnfReq as String
Dim AcroPDDocNew as Acrobat.AcroPDDoc
Dim AcroPPDocAdd as Acrobat.AcroPDDoc
Dim IRet as Long
Dim IGetNumPages as Long
Dim IPages as Long
Dim whereStr as String

reportName = "Nominations"
initialFolderLocation = "C:\Users\jjvale\Desktop"

fileName = initialFolderLocation & reportName & "_" & Format(Date, YYYYNNMM") & ".pdf"
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName, False
DoCmd.Close acReport, reportName

Set dbCurr = CurrentDb()
reportNameCnf = "CNF Report"
strCnf = "SELECT Cnf.[CNF ID#], cnf.Title, Cnf.Status " _
        & "FROM Cnf " _
        & "WHERE (((Cnf.Status) Like 'Ready*'));"
Set rsCnf = dbCurr.OpenRecordset(strCnf)
While rsCnf.EOF = False
    cnfId = rsCnf![CNF ID#]
    cnfTitle = DLookup("[Title]", "[Cnf]", "[CNF ID#] = " & cnfId)
    I = InStrRev(cnfTitle, ")")
    cnfTitle = Mid(cnfTitle, I + 1)
    cnfTitle = RTrim(cnfTitle)
    cnfTitle = Replace(cnfTitle, "/", " ")
    cnfTitle = Replace(cnfTitle, "\", " ")
    cnfTitle = Replace(cnfTitle, ":", " ")
    cnfTitle = Replace(cnfTitle, "*", " ")
    cnfTitle = Replace(cnfTitle, "?", " ")
    cnfTitle = Replace(cnfTitle, "&", " ")
    cnfTitle = Replace(cnfTitle, Chr(34), " ")
    cnfTitle = Replace(cnfTitle, "<", " ")
    cnfTitle = Replace(cnfTitle, ">", " ")
    cnfTitle = Trim(cnfTitle)
    fileNameCnf = initialFolderLocation & "CNF " & cnfId & " - " & cnfTitle & ".pdf"
    whereStr = "[CNF ID#] IN (" & cnfId & ")"
    DoCmd.OpenReport reportnameCnf, acViewPreview, , whereStr, acHidden
    DoCmd.OutputTo acOutputReport, reportNameCnf, acFormatPDF, fileNameCnf, False
    DoCmd.Close acReport, reportNameCnf
    rsCnf.MoveNext
Wend
CnfReq = Dir(initialFolderLocation & "CNF*")

IRet = AcroPDDocNew.Create()

IRet = AcroPDDocAdd.Open(file path for nominations report)
IGetNumPages = AcroPDDocAdd.GetNumPages()
IRet = AcroPDDocNew.InsertPages(IPages-1, AcroPDDocAdd, 0, IGetNumPages, True)
IRet = AcroPDDocAdd.Close

IPages = IPages + IGetNumPages

IRet = AcroPDDocAdd.Open(cnfReq)
IGetNumPages = AcroPDDocAdd.GetNumPages()
IRet = AcroPDDocNew.InsertPages(IPages-1, AcroPDDocAdd, 0, IGetNumPages, True)
IRet = AcroPDDocAdd.Close

IRet = AcroPDDocNew.Save(1, "C:\Users\jjvale\Desktop\Testing.pdf")
IRet = AcroPDDocAdd.Close()

Set AcroPDDocAdd = Nothing
Set AcroPDDocNew = Nothing

MsgBox = "The Nominations have been printed out."