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

[GirishDhruva]

Hi Everyone,
Here i am trying to combine two PDF sheets using Adobe, where the below code runs perfectly but

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

 

[John_w]

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).

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]

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]

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

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

even i tried with

If primaryDoc.Save(PDSaveFlags.PDSaveFull, PDFs(i, 1)) Then

then also same error

Regards
Dhruv

 

[John_w]

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]

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]

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:

 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]

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:

 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]

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.

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

 

[IlanL]

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.