VBA export sheets as PDFs, merge with existing PDFs

Hi gurus,

I am currently using the below code to export sheets in a workbook as PDFs. Each PDF's Filename is the name of the exported sheet. The FolderPath is C:\PDFs\Bi-Weekly Syndication Reports.

Please Login or Register  to view this content.

This code results in an error if the FolderPath already exists so I have to delete it each time before running the macro (annoying). This is what I am trying to achieve:

• If the FolderPath does NOT exist, create the FolderPath.

• If the FolderPath does exist, follow the FolderPath.

• If a PDF is exported with a Filename that does NOT exist in the folder, create the file.

• If a PDF is exported with a Filename that does exist in the folder, merge the new PDF with the existing PDF.

Any help would be MUCH appreciated! Thanks in advance to anyone who answers!

Thanks for the prompt response Kenneth. I just downloaded and installed PDFCreator. Unfortunately I don't know what to do with your above code.

Could you please elaborate on how to integrate it with my existing code? Appreciate it!

I showed 2 ways to use PDFCreator in the referenced thread. Which version did you download and install? The newer version is slow.

Just checked and I actually got the 2.1.2 version. Sorry I'm new to using vba while utilizing 3rd party software so I don't understand your code or how to integrate it with my current stuff :/

To use my routine, you need to build the string array with your file names and pass the name of the merged pdf that you want to create. Before I show you the exact method, why not try a test to merge two of your files? Simply replaced the values in the test routine. Be sure to set the reference to PDFCreator as I explained.

After you try this, I will show you how to build the string array if needed. Keep in mind that Adobe Reader will open for each pdf file. Either close each or take a coffee break and it will close them for you. As I said, it is not perfect but does work, eventually. Be sure that you can live with it before I show you more.

Please Login or Register  to view this content.

I've been trying for a few days now to get your code to work for me. All that happens is a black box appears for a second then disappears.

For me to get this going I think you'd have to dumb the process down a lot for me.

I understand if you don't have the time to do this. Thanks a lot for your help so far regardless.

Adding the reference and the two existing file names and paths and the name and filepath to merge the two files to seems about as simple as one can get.

As I said, it is not the fasted way to go but does work. IF you can't get the simple example, the more complete solution won't work either.

Feel free to attach a simple file with the code that you tried and I will check it out with my file paths.