Combine two PDF into one Separate PDF and save in particular path Using VBA

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
276
Hi Everyone,
Here i am trying to combine two PDF sheets using Adobe, where the below code runs perfectly but
Code:
Sub Combine_PDF()
    Dim x, lastrow As Long
    Dim arrayFilePaths() As Variant
    Set app = CreateObject("Acroexch.app")
    
    lastrow = Sheets("Combine_PDF").Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To lastrow
        path1 = Cells(x, "A").Value
        path2 = Cells(x, "B").Value
        arrayFilePaths = Array(path1, path2)
        
        Set primaryDoc = CreateObject("AcroExch.PDDoc")
        OK = primaryDoc.Open(arrayFilePaths(0))
        Debug.Print "PRIMARY DOC OPENED & PDDOC SET: " & OK
        
        For arrayIndex = 1 To UBound(arrayFilePaths)
            numPages = primaryDoc.GetNumPages() - 1
            
            Set sourceDoc = CreateObject("AcroExch.PDDoc")
            OK = sourceDoc.Open(arrayFilePaths(arrayIndex))
            Debug.Print "SOURCE DOC OPENED & PDDOC SET: " & OK
            
            numberOfPagesToInsert = sourceDoc.GetNumPages
            
            OK = primaryDoc.InsertPages(numPages, sourceDoc, 0, numberOfPagesToInsert, False)
            Debug.Print "PAGES INSERTED SUCCESSFULLY: " & OK
            
            OK = primaryDoc.Save(PDSaveFull, arrayFilePaths(0))
            Debug.Print "PRIMARYDOC SAVED PROPERLY: " & OK
            
            Set sourceDoc = Nothing
        Next arrayIndex
        
        Set primaryDoc = Nothing
    Next x
    app.Exit
    Set app = Nothing
    MsgBox "DONE"
End Sub
but can anyone suggest me how i can combine two PDF sheets and save that sheet in separate PDF file and in separate path
Is their any possible ways to do that?????
Please do suggest me with this

Regards
Dhruv
 
Last edited:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,323
Re: How can we combine two PDF into one Separate PDF and save in particular path Using VBA

Try this macro, which expects the full path and file name of the 2 input PDFs to be in column A and B and the output PDF to be in column C. Or change every PDFs(i, 3) to PDFs(i, 1) if you want the output PDF to be the same as column A (overwriting the original file).

Code:
Public Sub Combine_PDFs()
    
    Dim PDFs As Variant, i As Long
    Dim primaryDoc As Object, sourceDoc As Object
    Dim numPages As Long, numberOfPagesToInsert As Long
    
    With Sheets("Combine_PDF")
        PDFs = .Range("A2:C" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    
    Set primaryDoc = CreateObject("AcroExch.PDDoc")
    Set sourceDoc = CreateObject("AcroExch.PDDoc")
    
    For i = 1 To UBound(PDFs)
        If primaryDoc.Open(PDFs(i, 1)) Then
            numPages = primaryDoc.GetNumPages() - 1
            If sourceDoc.Open(PDFs(i, 2)) Then
                numberOfPagesToInsert = sourceDoc.GetNumPages
                If primaryDoc.InsertPages(numPages, sourceDoc, 0, numberOfPagesToInsert, False) Then
                    If primaryDoc.Save(PDSaveFlags.PDSaveFull, PDFs(i, 3)) Then
                        MsgBox "Created " & PDFs(i, 3)
                    Else
                        MsgBox "Error saving " & PDFs(i, 3)
                    End If
                Else
                    MsgBox "Error inserting pages from " & PDFs(i, 2) & " into " & PDFs(i, 1)
                End If
                sourceDoc.Close
            Else
                MsgBox "Error opening Source PDF " & PDFs(i, 2)
            End If
            primaryDoc.Close
        Else
            MsgBox "Error opening Primary PDF " & PDFs(i, 1)
        End If
    Next
    
    Set sourceDoc = Nothing
    Set primaryDoc = Nothing
    
    MsgBox "DONE"
    
End Sub
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
276
Re: How can we combine two PDF into one Separate PDF and save in particular path Using VBA

Hi John_w thanks for your reply, I am out for next two days so once I reach back I will check and confirm you

Regards
Dhruv
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
276
Re: How can we combine two PDF into one Separate PDF and save in particular path Using VBA

Hi John_w it's throwing me an run time error 424 as object required in below line
Code:
If primaryDoc.Save(PDSaveFlags.PDSaveFull, PDFs(i, 3)) Then
even i tried with
Code:
If primaryDoc.Save(PDSaveFlags.PDSaveFull, PDFs(i, 1)) Then
then also same error

Regards
Dhruv
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,323
Re: How can we combine two PDF into one Separate PDF and save in particular path Using VBA

I guess you're not using Option Explicit and haven't set a reference to the Adobe Acrobat Type Library. In that case, change PDSaveFlags.PDSaveFull to 1 and it should work.

You should always use Option Explicit at the top of every module because it forces you to declare (Dim) variables and makes investigating compilation errors and run-time errors such as this one far easier. To have Option Explicit included automatically at the top of every module, in the VBA editor click Tools - Options -> Editor tab - tick Require Variable Declaration.
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
276
Re: How can we combine two PDF into one Separate PDF and save in particular path Using VBA

I guess you're not using Option Explicit and haven't set a reference to the Adobe Acrobat Type Library. In that case, change PDSaveFlags.PDSaveFull to 1 and it should work.

You should always use Option Explicit at the top of every module because it forces you to declare (Dim) variables and makes investigating compilation errors and run-time errors such as this one far easier. To have Option Explicit included automatically at the top of every module, in the VBA editor click Tools - Options -> Editor tab - tick Require Variable Declaration.
Hi @John_w ,It worked and thanks for you valuable information and suggestions and sure from now i will always use Option Explicit.

Regards
Dhruv
 

IlanL

New Member
Joined
Nov 22, 2019
Messages
3
Office Version
365
Platform
Windows
Hello @John_w.
Thank you for the help above.
I was wondering if I could get your assistant as well with respect to error 424.

I followed the above code and seems to get stuck at the same ".save" statement.
This is a recurring issue that I keep encountering.
My option explicit is working and yet, not able to pass through that statement.
Also tried to change change PDSaveFlags.PDSaveFull to 1 without luck.
This is what I did (just to make sure that I understood your instructions:
VBA Code:
 If primaryDoc.Save(1, PDFs(i, 3)) Then
I was wondering maybe something in my settings is in charge for that issue.

Also, with respect to :

primaryDoc.Save(PDSaveFlags.PDSaveFull, PDFs(i, 3))

Should the path part include the filename+.pdf? or only the file's path?

Thank you in advance.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,323
Hello @John_w.
Also tried to change change PDSaveFlags.PDSaveFull to 1 without luck.
This is what I did (just to make sure that I understood your instructions:
VBA Code:
 If primaryDoc.Save(1, PDFs(i, 3)) Then
Yes, that change is correct. Also remember that the code uses the Acrobat API library, which is only available when Acrobat Pro (not the free Adobe Reader) is installed.

Also, with respect to :

primaryDoc.Save(PDSaveFlags.PDSaveFull, PDFs(i, 3))

Should the path part include the filename+.pdf? or only the file's path?
I refer you to my first post in this thread, answering the OP's question:
Try this macro, which expects the full path and file name of the 2 input PDFs to be in column A and B and the output PDF to be in column C. Or change every PDFs(i, 3) to PDFs(i, 1) if you want the output PDF to be the same as column A (overwriting the original file).
Therefore PDFs(i, 3) refers to the full PDF file name (folder path, file name and .pdf extension) in the column C cell(s).
 

IlanL

New Member
Joined
Nov 22, 2019
Messages
3
Office Version
365
Platform
Windows
Hello John, and thank you for your response.

I work with acrobat dc so am good with it.

I couldn't detect the issue source for the error.
My main goal was to populate a PDF template with information from an excel sheet. (separate pdf file for each excel row)
Then to save each row into a new pdf file.
I managed to complete my goal with another macro found.
I will share it below for other users looking to automate population of pdf template and print with a unique file name.

Thanks again.


Code:
Sub Save_PDF_Work


Dim FileNm, gApp, avDoc, pdDoc, jso
Dim NewFilename
Dim i As Byte
Dim ID As String
i = 12


For i = 12 To 15
FileNm = "Insert path\filename.pdf"
NewFilename = Sh1.Range("A1").Value

Set gApp = CreateObject("AcroExch.app")

Set avDoc = CreateObject("AcroExch.AVDoc")


ID = Sh1.Cells(i, 1).Value

If avDoc.Open(FileNm, "") Then

    Set pdDoc = avDoc.GetPDDoc()

    Set jso = pdDoc.GetJSObject
  
    jso.getfield("A Identifying number").Value = ID

    pdDoc.Save PDSaveCopy, NewFilename & Cells(i, 2).Value & ".pdf"
  
    pdDoc.Close

End If


avDoc.Close (True)
Set gApp = Nothing
Set avDoc = Nothing
Set pdDoc = Nothing
Set jso = Nothing
Next i

End Sub
 
Last edited by a moderator:

IlanL

New Member
Joined
Nov 22, 2019
Messages
3
Office Version
365
Platform
Windows
Hi John,

I managed to save the pdf file, however it saves it as accessible pdf file.

How can I save it as non accessible pdf file?
I would assume the the following statement should be tweaked:

PdfDoc.Save PDSaveFull

Thanks for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,784
Messages
5,488,860
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top