PDF manipulation with Access VBA

[Sonic76]

Hi,
My Access DB prints out thousands of PDF reports a month.
I need to merge them by name together.

I need help with the PDF part. The rest (VBA, ADODB ) I have covered.

What do i need to merge PDF's together. Do i need a dll, ocx etc'? Which commands should i use?

Thank you

 

[AlexCuse]

I know when you select several .pdf files and right click on them, you get an option to "combine in Adobe Acrobat"

I have a feeling you could programmatically specify a directory, grab each file name in the directory (where right 4 = ".pdf") and then send a command to run distiller using the collection of .pdf's as your source.

I have used a similar piece to this:

varShell = Shell("c:\Program Files\Adobe\Acrobat 6.0\Distillr\Acrodist.exe /n /q" & strOutputFileName, vbNormalFocus)

(Thanks, CautionMP) to create files (of the same name) from post script files. Not sure how you'd do it using .pdf's as a source though. Please post your solution when you find it.

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[CautionMP]

Sonic76,
If you are using Adobe Acrobat you can add a reference to the Adobe Acrobat X.x Type Library
(C:\Program Files\Adobe\Acrobat X.x\Acrobat\Acrobat.tlb). Then using the [tt]CAcroPDDoc[/tt] Class you can [tt]Open()[/tt] the first document then [tt]InsertPages()[/tt] from other documents to create a new combined document.

I don't have a code sample (since the Adobe SDK is not free and I've yet to talk a client into buying it for me), but the above methods may help in locating a working code snipit when searching.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

Have I mentioned recently how much I don't like the Adobe?

Anyway, had some spare time so here is a rough shell that should do the trick. I combined two 2 page documents to create a new 4 page document and it took about 12 seconds. I suspect that the majority of that time was spent creating the two [tt]AcroExch.PDDoc[/tt] objects so your code should run faster, but not fast.

Sub TestCombinePDF()
'Relies on the Adobe Acrobat 6.0 Type Library
Dim objCAcroPDDocDestination As Acrobat.CAcroPDDoc
Dim objCAcroPDDocSource As Acrobat.CAcroPDDoc

'Initialize the objects
Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")

'Open Destination, all other documents will be added to this and saved with
'a new filename
objCAcroPDDocDestination.Open ("C:\Source1.pdf")

[b]'Do your loop here to open subsequent documents that you want to add
'Do[/b]
  'Open the source document that will be added to the destination
  objCAcroPDDocSource.Open ("C:\Source2.pdf")
  If objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
    '-1 Success
  Else
    '0 problem
  End If
  objCAcroPDDocSource.Close
[b]'loop[/b]

objCAcroPDDocDestination.Save 1, "C:\Destination.pdf"
objCAcroPDDocDestination.Close
Set objCAcroPDDocSource = Nothing
Set objCAcroPDDocDestination = Nothing
End Sub

Good luck,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Sonic76]

Thanks CMP!

I constantly receive:
"ActiveX component can't create object or return reference to this object (Error 429)"

I added Adobe Acrobat 7.0 type library to my refrences.
Am i missing a DLL?

Regards,
Sonic76

 

[rr191]

I use a commercial product called PDF and Mail Library for MS-Access from ACGSoft (

http://www.groupacg.com/).

This may be cost effective if you only have one or two PCs it needs to run on.

 

[CautionMP]

Sonic76,
No, you just have the newer copy of Acrobat, and of course it's slightly different. Here are a couple of thoughts:
[ul][li]Do you get an error when you try and Compile the code, or only when you run it?[/li]
[li]Where exactly do you get the error?[/li][/ul]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Sonic76]

CMP,

I receive this error only when i run the code.

I receive it on

Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")

Thanks,
sonic76

 

[CautionMP]

Sonic76,
I looked at a couple posts and it appears that [tt]AcroExch.PDDoc[/tt] still exists in version 7 but you never know with Adobe.

I would check the registry and see if you have the [tt]AcroExch.PDDoc[/tt] listed in [tt]HKEY_CLASSES_ROOT[/tt] (you probably don't if your getting this error), is there a variation that ends [tt].PDDoc[/tt]?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Sonic76]

Your're right CMP. Couldn't find the AcroExch.PDDoc

(I found:
HKEY_CLASSES_ROOT\AcroExch.XDPDoc)

No other .PDDoc present.

How can i correct this?