VBA to combine PDFs

bxp265

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I am trying to combine linked PDFs in my excel document. Below is my code but it does not work. Any thoughts or suggestions?

Option Explicit

Sub insert_PDF()

Dim AcroApp As Acrobat.CAcroApp
Dim targetpdf As Acrobat.CAcroPDDoc
Dim sourcePDF As Acrobat.CAcroPDDoc
Dim i As Long
Dim j As Long
Dim sLink As String
Dim success As Long
Dim counter As Long
Dim last_row As Long
Dim sourceNumPages As Long
Dim targetNumPages As Long

Set AcroApp = CreateObject("AcroExch.App")
Set targetpdf = CreateObject("AcroExch.PDDoc")

'find the last non-blank row in Column A
'there should be no blank cells before the last cell in Column A
last_row = Sheets(1).Range("A65536").End(xlUp).Row

'open the first link whose I column value is not "no"
For i = 2 To 34
If UCase(Sheet2.Range("J" & i).Value) = "YES" Then
If Range("A" & i).Hyperlinks.Count > 0 Then
' Set hyperlink in column A if it exists
sLink = Range("A" & i).Hyperlinks(1).Address

success = targetpdf.Open(sLink)
Exit For
End If
End If
Next i

'if column I has no "yes"
If i = 34 Then
MsgBox ("Cannot find yes. No link to open. Exit program....")
Exit Sub
End If

'find the number of pages in the target PDF file
targetNumPages = targetpdf.GetNumPages

'insert files to targetPDF from sourcePDF
Set sourcePDF = CreateObject("AcroExch.PDDoc") 'content to be pasted

For counter = i + 1 To 34

If UCase(Sheet2.Range("J" & i).Value) = "YES" Then
sLink = Range("A" & i).Hyperlinks(1).Address
success = sourcePDF.Open(sLink)

sourceNumPages = sourcePDF.GetNumPages
j = targetpdf.InsertPages(targetNumPages - 1, sourcePDF, 0, sourceNumPages, 0)
End If

sourcePDF.Close

Next counter
Debug.Print "targetpdf"
'save file to C:\temp\MergedFile.pdf <- need to be changed to suit your need
If targetpdf.Save(PDSaveFull, "C:\Users\bpikus\Desktop\Test.pdf") = False Then
MsgBox "Cannot save the modified document"
End If

targetpdf.Close
sourcePDF.Close

AcroApp.Exit
Set AcroApp = Nothing
Set targetpdf = Nothing
Set sourcePDF = Nothing

MsgBox "Done"

End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
409
Office Version
  1. 365
Platform
  1. Windows
When you say it's not working, what's happening? Is it throwing up an error message? Is it coming up with a result different to what you intended? I could be wrong, but your code requires Adobe Acrobat Pro.
 

bxp265

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Web
It doesn't merge any of the PDFs. It says "Cannot save the modified document" and then "Done" which is what I have my message boxes set up to say. It doesn't actually open or merge any of the PDFs that it's supposed to. I do have Adobe Acrobat Pro.
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,856
Office Version
  1. 2010
Platform
  1. Windows
What are the values of success and j? They'll tell you if those two steps work or not.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,395
Messages
5,636,054
Members
416,894
Latest member
Hari1992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top