VBA to Combine PDFs based on Yes/No Criteria

[bxp265]

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.

 

[cortexnotion]

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]

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]

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]

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]

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.

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]

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

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!

 

[John_w]

Add a reference to Adobe Acrobat n.0 Type Library, where n is your Acrobat version number, via Tools -> References.

 

[bxp265]

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]

Do you mean that reference isn't listed? The code requires Acrobat Professional to be installed and then the reference will be listed.