Using VBA to create/append to PDF

[chicken_po_boy]

I'm a hack at VBA and I could use some help. I have macros like this:

MacroA()
set up
prints selection A to printer
End

MacroB()
set up
prints selection B to printer
End

MacroC()
set up
prints selection C to printer
End

Then I have another macro called "PrintAll"...

Macro PrintAll
call MacroA()
call MacroB()
call MacroC()
End

(The real situation is more complex than this.)

I want to change PrintAll to save everything to a single PDF rather than print to the printer. However, I only want a PDF if running PrintAll. If running individial A/B/C macros (by themselves) I want them to continue printing to the printer as they currently do.

For creating the PDF, I want to utilize the print setups within MacroA, MacroB, and MacroC so I still want to call those macros from PrintAll. I'm halfway there. I changed the macros like this:

Macro PrintAll
All = 1
call MacroA(All)
call MacroB(All)
call MacroC(All)
All = 0
End

MacroA(All)
set up
If All = 1
save as .pdf
Else
prints selection A to printer
End If
End

MacroB and MacroC are similar to MacroA.

This is correctly printing to printer when running the individual macros, and making PDF's when running the PrintAll macro. Problem is... the PDF's are individual documents. I don't know how to combine the PDF's into a single document. Is there a way to have VBA "append" to a PDF? Or, is there a way to write to a PDF, leave it open, and write some more before saving/closing?

[llcooljabe]

I tried something similar years ago. I posted in the MrExcel.com message boards: http://www.mrexcel.com/forum/showthread.php?t=297737

I never ended up using the suggested solutions, but check them out.

[Chuck]

Write all the selections to another document (say a Word doc or another Excel tab with page breaks) and then print that document?

[chicken_po_boy]

I found some code on the internet to append PDF's but it is not working for me, grrr... Here is where I found the code:

http://www.vbaexpress.com/forum/showthread.php?t=21610

I'm getting an error message "Run-time error '429': ActiveX component can't create object" (whatever the heck that means)

I'm attaching a watered-down version sample file to illustrate the problem. It will create two .pdf's and save them (create a C:\temp directory before running so it can save them there). But then it crashes when I try to use the function to append them together. Can anyone help? I feel like I'm so close... Thanks!!

P.S. "PrintA" will print Tab A to the printer and "PrintB" will print Tab B to the printer (just how I want it). "PrintAll" should create a .pdf of both; this is the part that's not working.

[dumples]

Originally Posted by chicken_po_boy

I found some code on the internet to append PDF's but it is not working for me, grrr... Here is where I found the code:

http://www.vbaexpress.com/forum/showthread.php?t=21610

I'm getting an error message "Run-time error '429': ActiveX component can't create object" (whatever the heck that means)

I'm attaching a watered-down version sample file to illustrate the problem. It will create two .pdf's and save them (create a C:\temp directory before running so it can save them there). But then it crashes when I try to use the function to append them together. Can anyone help? I feel like I'm so close... Thanks!!

P.S. "PrintA" will print Tab A to the printer and "PrintB" will print Tab B to the printer (just how I want it). "PrintAll" should create a .pdf of both; this is the part that's not working.

Did you add a reference to the appropriate library? From a little bit of googling of "AcroExch", I think that should be the "Adobe Acrobat Type Library", but I don't have that in my reference list (I do have Acrobat 8 installed on this computer). I have a few things that look like it, but I'm not going to mess around with this for you.

To access references, Go to the VBA editor, Tools>References

[dumples]

Try adding the "Acrobat" reference and let me know if that code works.

[dumples]

In your code, you have a line that says:
"Dim Final As Object"

change that to
"Dim Final As Boolean"

Then it works for me without messing with any references, just the 4 that are usually there.

[dumples]

These are the 4 references I have.

I am using office 2010 and I have adobe acrobat installed on my computer. I'm not sure if that makes a difference.

[chicken_po_boy]

I added a whole bunch of references (anything that looked like it might have something to do with Acrobat), but unfortunately it is still not working. This is new territory for me... Do I need to have the full version of Acrobat? I only have the reader version.

[chicken_po_boy]

I just tried it on someone else's computer (who has the full version of Acrobat) and it worked. Thanks!!