VBA to Combine PDFs based on Yes/No Criteria

bxp265

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi,

I am new to VBA and excel macros. I have an excel document that is set up so column "A" contains hyperlinks to PDFs. In column "I" there is a yes or no. If there is a yes, then I want the PDF in column A to be added to a master PDF document. The idea is that this master PDF document contains all PDFs for which there was a "yes" in column "l".

Does this make sense and can anyone help me? I have successfully written a macro for each PDF to open individually, but I have not been able to merge them or print them. Any help would be MUCH appreciated.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

cortexnotion

Board Regular
Joined
Jan 22, 2020
Messages
128
Office Version
  1. 2013
Platform
  1. Windows
Hi @bxp265,

I have looked to use Excel to merge PDF documents myself and through research I believe this can only be done if you have Adobe Acrobat (not just Reader) installed. If you have I'm sure someone more knowledgeable will help with some nifty code :)
 

bxp265

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi @bxp265,

I have looked to use Excel to merge PDF documents myself and through research I believe this can only be done if you have Adobe Acrobat (not just Reader) installed. If you have I'm sure someone more knowledgeable will help with some nifty code :)

Good to know. Thank you!
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,743
Office Version
  1. 2010
Platform
  1. Windows
If you can write a macro to open PDF, then, you just need the Acrobat API file, go through it and see how to insert pages. Actually, just search the document for "insert" and see how to use the API.

You can download the Acrobat API document here.
 

bxp265

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Web

ADVERTISEMENT

If you can write a macro to open PDF, then, you just need the Acrobat API file, go through it and see how to insert pages. Actually, just search the document for "insert" and see how to use the API.

You can download the Acrobat API document here.
Thank you! Do you know how I would incorporate the yes/no criteria from my excel?
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,743
Office Version
  1. 2010
Platform
  1. Windows
Try this. This script saves the merged file as C:\temp\Mergedfile.pdf. You want to change it to suit your needs.
I only tried four links and they are all in the same directory - C:\temp. It should work for links in different directories.

mergepdf.png


VBA Code:
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 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 = 1 To last_row Step 1
    
        If Not Sheets("Sheet1").Cells(i, 9) = "no" Then
            success = targetpdf.Open(Sheets("Sheet1").Cells(i, 1).Value)
            Exit For
        End If
    Next i
    
    'if column I has no "yes"
    If i = last_row 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 last_row
    
        If Sheets("Sheet1").Cells(counter, 9).Value = "yes" Then
    
            success = sourcePDF.Open(Sheets("Sheet1").Cells(counter, 1).Value)

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

    Next counter

    'save file to C:\temp\MergedFile.pdf <- need to be changed to suit your need
    If targetpdf.Save(PDSaveFull, "C:\temp\MergedFile.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
 

bxp265

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Web

ADVERTISEMENT

Try this. This script saves the merged file as C:\temp\Mergedfile.pdf. You want to change it to suit your needs.
I only tried four links and they are all in the same directory - C:\temp. It should work for links in different directories.

View attachment 17480

VBA Code:
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 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 = 1 To last_row Step 1
   
        If Not Sheets("Sheet1").Cells(i, 9) = "no" Then
            success = targetpdf.Open(Sheets("Sheet1").Cells(i, 1).Value)
            Exit For
        End If
    Next i
   
    'if column I has no "yes"
    If i = last_row 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 last_row
   
        If Sheets("Sheet1").Cells(counter, 9).Value = "yes" Then
   
            success = sourcePDF.Open(Sheets("Sheet1").Cells(counter, 1).Value)

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

    Next counter

    'save file to C:\temp\MergedFile.pdf <- need to be changed to suit your need
    If targetpdf.Save(PDSaveFull, "C:\temp\MergedFile.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


Thank you! I would not have figured that out. Do you know what the issue is here? I've attached a photo. I feel like it's so close to working!
 

Attachments

  • User type not defined.PNG
    User type not defined.PNG
    36.8 KB · Views: 3

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,465
Add a reference to Adobe Acrobat n.0 Type Library, where n is your Acrobat version number, via Tools -> References.
 

bxp265

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Add a reference to Adobe Acrobat n.0 Type Library, where n is your Acrobat version number, via Tools -> References.
Thanks, John. It's not letting me add a reference. Any idea why?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,465
Do you mean that reference isn't listed? The code requires Acrobat Professional to be installed and then the reference will be listed.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,086
Messages
5,545,877
Members
410,711
Latest member
Josh324
Top