Access 2007 PDF merge via VBA without third party software

Hello All,
I have a need to merge several PDF reports together on a daily basis via VBA.  I don't want to use any third party software unless it's free and can be done through VBA coding.  I have a query that generates the path and file name of all the reports that need to be merged, I just need to be able to merge them into one document.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OnisanCoder / ProgrammerCommented:
This is going to depend on a a few things, are the PDF's all produced by the same system?
What type of PDF's are they, As in how are they compressed (flateDecode, DTC, etc.)
Are they image PDF's or text? if they are files you've received via fax then they are basically tiff files.
do you have Acrobat Professional loaded / Office 2010 or above?

Depending on the above will depend on whether this is doable. It's a huge undertaking.
I've worked extensively with PDF's and the file structure so I can tell you that this is more than likely going to be too much of a project for VBA. however, what is the end goal? is it to store all PDF's in one place, file? if so how about embedding them in another document or zip file?
Gustav BrockCIOCommented:
The very very simple solution is to print the pdf files one by one in sequence to an installed PDF printer which is set to print to a new PDF file with the Overwrite/Append option set to "Append".

VBscript might be used for this.

shannondsAuthor Commented:
Onisan - The files are actually Access 2007 reports printed to PDF using the "DoCmd.OutputTo" function.  The computer does NOT have Acrobat Professional loaded or Office 2010 (It has Office 2007).  The goal is as follows.  We have 3 types of reports.  One for Territory Managers, one for District Managers and one for Regional Managers.  We'd like all the territory reports that fall under a given district to be merged with the District report and all the districts that fall under a given region to be merged with the Region report.  I don't think my boss wants to do this via Zip file, but I'll run it by him.

Gustav Brock - I'm not sure what you mean.  Could you supply a VBscript example?

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

OnisanCoder / ProgrammerCommented:
An installed PDF Printer would be "Third Party Software", as there isn't a standard PDF printer driver distributed with Windows.
another suggestion is to output the required reports to excel all at once and save them from there to PDF or to output all as a single report (this would require some development but depending on the complexity of the reports may not be too difficult) If all reports use the same query but with different perams (ie region) then you could make a single query from them all with all regions as a UNION query and base a report off that which you can then output as a single PDF. this method would give the illusion of multiple PDF's merged as you can have the "Header" loade per region in a single report.

Let me know what you think, happy to assist further but I am logging off for the night now.
All the best
shannondsAuthor Commented:
Hi Gary, Thanks for the info.  I was looking into creating a merged report in Access and then outputting that to PDF, but it's kind of a pain due to the number of reports and the hierarchy of Territories to Districts and Districts to Regions.  If it was only one report that needed to be generated then the source wouldn't be that big a deal, but there are many.  I just heard from my boss and I'm going to look into a free program called "PDF Split and Merge".  Supposedly it's got a command line interface that I can use.  I just have to figure out the syntax and I should be fine.

I'll let you know what the results are.
Gustav BrockCIOCommented:
The command line core i here, should anybody else wish to know:

shannondsAuthor Commented:
I'm actually all set now.  Thanks anyway.
I ended up using "PDFtk Free"

It has a command line option which was pretty simple to use.

pdftk "in1.pdf" "in2.pdf" cat output "out1.pdf"

It does exactly what I needed and works extremely fast.  
Thanks again for the suggestions...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shannondsAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for shannonds's comment #a40726226

for the following reason:

I ended up going with a free 3rd part solution.  After explaining the issues with my boss, he agreed that this would be the best way of dealing with it.
Gustav BrockCIOCommented:
Thanks for the feedback.

OnisanCoder / ProgrammerCommented:
I offered solutions and asked questions to aid getting to a result so does this not mean I should be apportioned at least some points for this? Had the user been happy with a third party solution initially I would have been able to supply him with various options but instead I stuck to the topic and question as asked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.