Consulting

Results 1 to 2 of 2

Thread: Combine multiple pdf files into one file using Excel

  1. #1

    Combine multiple pdf files into one file using Excel

    Hello, can someone help me, I'm trying to combine multiple pdf files into one file. I found the code below that gets me almost there but I'm stuck on how to combine all pdf files in column A of an excel spreadsheet, the tricky part is that each time I need to run the macro, the number of files that need to be combined changes. Any help would be much appreciated!

    Sub Main()
    Dim MyFiles As String, DestFile As String
    With ActiveSheet
    MyFiles = .Range("A1").Value & "," & .Range("A2").Value
    DestFile = .Range("A3").Value
    End With
    Call MergePDFs01(MyFiles, DestFile)
    End Sub

    Sub MergePDFs01(MyFiles As String, DestFile As String)
    ' Reference required: VBE - Tools - References - Acrobat

    Dim a As Variant, i As Long, n As Long, ni As Long
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.AcroPDDoc

    a = Split(MyFiles, ",")
    ReDim PartDocs(0 To UBound(a))

    On Error GoTo exit_
    If Len(Dir(DestFile)) Then Kill DestFile
    For i = 0 To UBound(a)
    ' Check PDF file presence
    If Dir(Trim(a(i))) = "" Then
    MsgBox "File not found" & vbLf & a(i), vbExclamation, "Canceled"
    Exit For
    End If
    ' Open PDF document
    Set PartDocs(i) = New Acrobat.AcroPDDoc ' CreateObject("AcroExch.PDDoc")
    PartDocs(i).Open Trim(a(i))
    If i Then
    ' Merge PDF to PartDocs(0) document
    ni = PartDocs(i).GetNumPages()
    If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
    MsgBox "Cannot insert pages of" & vbLf & a(i), vbExclamation, "Canceled"
    End If
    ' Calc the amount of pages in the merged document
    n = n + ni
    ' Release the memory
    PartDocs(i).Close
    Set PartDocs(i) = Nothing
    Else
    ' Calc the amount of pages in PartDocs(0) document
    n = PartDocs(0).GetNumPages()
    End If
    Next

    If i > UBound(a) Then
    ' Save the merged document to DestFile
    If Not PartDocs(0).Save(PDSaveFull, DestFile) Then
    MsgBox "Cannot save the resulting document" & vbLf & DestFile, vbExclamation, "Canceled"
    End If
    End If

    exit_:

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

    ' Release the memory
    If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
    Set PartDocs(0) = Nothing

    ' Quit Acrobat application
    AcroApp.Exit
    'DoEvents: DoEvents
    Set AcroApp = Nothing

    End Sub

  2. #2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •