Print to PDF, Merge PDF

Hi guys I have a macro printing to pdf and then merging with another PDF. How can I save my DestFile to a different location than the folder where I grabbed the 2 PDF's to merge?

Sub PrintPages()

ThisWorkbook.Sheets(Array("Front Page", "1", "2", "Last Page")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"s:\Folder\Path\PDF2.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

    Const MyPath = "S:\Path\Folder\"
    Const MyFiles = "PDF1.pdf,PDF2.pdf"
    Const DestFile = "PDF1and2.pdf"

    Dim a As Variant, i As Long, n As Long, ni As Long, p As String
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc
    
    If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
    a = Split(MyFiles, ",")
    ReDim PartDocs(0 To UBound(a))
     
    On Error GoTo exit_
    If Len(Dir(p & DestFile)) Then Kill p & DestFile
    For i = 0 To UBound(a)

        If Dir(p & Trim(a(i))) = "" Then
            MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
            Exit For
        End If

        Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
        PartDocs(i).Open p & Trim(a(i))
        If i Then

            ni = PartDocs(i).GetNumPages()
            If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
                MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
            End If

            n = n + ni

            PartDocs(i).Close
            Set PartDocs(i) = Nothing
        Else

            n = PartDocs(0).GetNumPages()
        End If
    Next
     
    If i > UBound(a) Then

        If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
            MsgBox "Cannot save the resulting document" & vbLf & p & DestFile, vbExclamation, "Canceled"
        End If
    End If
     
exit_:
     
    If Err Then
        MsgBox Err.Description, vbCritical, "Error #" & Err.Number
    ElseIf i > UBound(a) Then
        MsgBox "The resulting file is created:" & vbLf & p & DestFile, vbInformation, "Done"
    End If

    If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
    Set PartDocs(0) = Nothing
     
    AcroApp.Exit
    Set AcroApp = Nothing

End Sub

Thx Jazz, I keep getting a Duplicate declaration in current scope comp error though?

Oh, that's because p is already declared. So you can use

p = "S:\Path\Other Folder\"

There are a few odd techniques in this code. For example, MyPath is a Const but there is an If statement checking to see if it ends in a "\".

What do you suggest to clean up the code? I am just looking to combine 2 pdf's in one folder and save the combined PDF in another folder

It's not wrong, it's just a little unusual and I'm not sure why it was done. I assume you did not write all this yourself. Try the change I suggested and see if that gets you what you need.

Thanks again Jazz, still not working tho, i'm sure something is wrong with my two folder locations and MyPath:

Sub PrintPages()
ThisWorkbook.Sheets(Array("Front Page", "Last Page")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"s:\Location1\PDF1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

    Const MyPath = "S:\Location 1\Folder\"
    Const MyFiles = "PDF1.pdf,PDF2.pdf"
    Const DestFile = "MERGED PDF.pdf"

    Dim a As Variant, i As Long, n As Long, ni As Long, p As String
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc
    
    If Right(p, 1) = "\" Then p = "S:\Location 2\Folder\"
    a = Split(MyFiles, ",")
    ReDim PartDocs(0 To UBound(a))
     
    On Error GoTo exit_
    If Len(Dir(p & DestFile)) Then Kill p & DestFile
    For i = 0 To UBound(a)

        If Dir(p & Trim(a(i))) = "" Then
            MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
            Exit For
        End If

        Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
        PartDocs(i).Open p & Trim(a(i))
        If i Then

            ni = PartDocs(i).GetNumPages()
            If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
                MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
            End If

            n = n + ni

            PartDocs(i).Close
            Set PartDocs(i) = Nothing
        Else

            n = PartDocs(0).GetNumPages()
        End If
    Next
     
    If i > UBound(a) Then

        If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
            MsgBox "Cannot save the resulting document" & vbLf & p & DestFile, vbExclamation, "Canceled"
        End If
    End If
     
exit_:

    If Err Then
        MsgBox Err.Description, vbCritical, "Error #" & Err.Number
    ElseIf i > UBound(a) Then
        MsgBox "The resulting file is created:" & vbLf & p & DestFile, vbInformation, "Done"
    End If

    If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
    Set PartDocs(0) = Nothing
     
    AcroApp.Exit
    Set AcroApp = Nothing
End Sub

Please explain what "not working" means.

• Does the code run?
• Does it run but do nothing?
• Does it produce error messages? If so, what do the messages say?
• Does it stop in the debugger? On what line of code?
• Does it produce unexpected/wrong results?
• Does it hang?

The PDF print is working to the right location but I get a "File not found" for PDF2.pdf here:

Const MyFiles = "PDF1.pdf,PDF2.pdf"

You can't possibly get a File Not Found on that line of code. It's just a declaration with a string assignment. What line is highlighted in yellow when the error occurs?