Append to pdf VBA

[diddi]

Morning all.
I am hoping to find advice for a Shell( ) or other method to add sheets to a pdf file.
I have tried several pdf generators / merging programs that support command line, but cannot get any of them to work.

I have a standard sheet which is filled by VBA and saved to PDF then repopulated and appended to the same PDF. There may be 30 or more pages to add.

I would prefer to use a free PDF program.
TIA

 

[Kenneth Hobson]

The string to pass to Shell() depends on the 3rd party application's command line options. In this thread, we discussed PDFsam and PDFtk. I also gave VBA code for PDFcreator and Acrobat.
https://www.mrexcel.com/forum/excel-questions/870539-combine-pdfs-using-vba.html

If you get stuck or need help with another application, post back.

 

[diddi]

thx for that. i did try PDFsam but could not get the shell script to work, so hopefully i can use your link but better success.

 

[lrobbo314]

I did something something similar to this using Python with a library called PyPDF2.

You could run VBA that would save each sheet in your workbook to a certain folder, then call open the Python script. The script opens a prompt which asks you to enter the folder where your PDFs are. Then it combines all of the PDFs into 1 file called Result.pdf.

Here's the Python Script.

from PyPDF2 import PdfFileMerger
import os
pdfs = []


path = input('Enter path of directory with PDFs to merge>>>')


for file in os.listdir(path + "\\"):
    if file.endswith('.pdf'):
        pdfs.append(path + "\\" + file)


merger = PdfFileMerger()


for pdf in pdfs:
    merger.append(pdf)


merger.write(path + "\" + 'Result.pdf')

 

[diddi]

thx for that. i will have another go

 

[Kenneth Hobson]

I have also used pdftk and pdfill in shell() routines.

This first version of pdftk is limited due to character string length limitations. If just a few to merge, this works ok. One could use chdir() and reduce string lengths.

'https://www.pdflabs.com/docs/pdftk-cli-examples/
'https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/
Sub pdftkMerge(arrayPDFs, pdfOut As String)
    Dim a, i As Long
    a = arrayPDFs
    For i = LBound(a) To UBound(a)
        a(i) = """" & a(i) & """"
    Next i
    'Command line options, https://www.pdflabs.com/docs/pdftk-man-page/
    '8191 character limit length for command line string.
    'Not sure what limit pdftk has, same probably.
    Shell "pdftk " & Join(a, " ") & " cat output " & """" & pdfOut & """", vbHide
End Sub

This one is more elaborate and shows how pdftk can merge the whole folder. I was merging each subolder in this example so yours would be much more simple. Post back if you need help to make it just for one folder.

'https://www.pdflabs.com/docs/pdftk-cli-examples/
Sub MergeToPDFtk2()
    Dim a, f, i As Long, p As String
    Dim p2 As String, r As String, fso As Object
    Dim s As String, k As String
    
    'Parent folder
    p = ThisWorkbook.Path & "\"
    p = "C:\Users\lenovo1\Dropbox\_Excel\pdf\Acrobat\"
    
    'Folder to copy merged pdfs in subfolders to, p2 initional, and r actual.
    p2 = p & "MergedPDFs"
    If Dir(p2, vbDirectory) = "" Then MkDir p2
    'Make a new folder in p2 to store this run's merged pdf files.
    Do
        i = i + 1
        r = p2 & "\Run" & i & "\"
        Loop Until Dir(r, vbDirectory) = ""
    MkDir r
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'SubFolders Array
    f = Split(CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
    """" & p & """" & " /ad/b/s").StdOut.ReadAll, vbCrLf)
    'Add parent folder to f:
    f(UBound(f)) = Left(p, Len(p) - 1)
    'Debug.Print Join(f, vbCrLf), "done"
    
    'Merge pdfs in subfolders, save merged file in r folder with subfolder's name.pdf.
    For i = 0 To UBound(f)
        k = f(i) & "\" & Dir(f(i) & "\*.pdf")
        If InStr(f(i), p2 & "\") = 0 And Dir(f(i) & "\*.pdf") <> "" Then
            'Need 2 pdfs to merge or pdfill slows and may error
            If Dir <> "" Then  'at least 2 pdfs files exist
                s = "pdftk " & _
                """" & f(i) & "\*.pdf" & """" & _
                " cat output " & """" & _
                """" & r & fso.GetFolder(f(i)).Name & ".pdf" & """"
                Shell s, vbNormal
                Else
                FileCopy k, (r & fso.GetFolder(f(i)).Name & ".pdf")
            End If
        End If
    Next i
    Set fso = Nothing
    MsgBox "PDF files merged to folder: " & r
End Sub

This one is similar to that above but uses pdfill.

'http://www.pdfill.com/pdf_batch_command.html
Sub MergeToPDFill()
    Dim a, f, i As Long, p As String
    Dim p2 As String, r As String, fso As Object
    Dim s As String, k As String
    
    'Parent folder
    'p = ThisWorkbook.Path & "\"
    p = "C:\Users\lenovo1\Dropbox\_Excel\pdf\Acrobat\"
    
    'Folder to copy merged pdfs in subfolders to, p2 initially, and r actual.
    p2 = p & "MergedPDFs"
    If Dir(p2, vbDirectory) = "" Then MkDir p2
    'Make a new folder in p2 to store this run's merged pdf files.
    Do
        i = i + 1
        r = p2 & "\Run" & i & "\"
        Loop Until Dir(r, vbDirectory) = ""
    MkDir r
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'SubFolders Array
    f = Split(CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
    """" & p & """" & " /ad/b/s").StdOut.ReadAll, vbCrLf)
    'Add parent folder to f:
    f(UBound(f)) = Left(p, Len(p) - 1)
    'Debug.Print Join(f, vbCrLf), "done"
    
    'Merge pdfs in subfolders, save merged file in r folder with subfolder's name.pdf.
    For i = 0 To UBound(f)
        k = f(i) & "\" & Dir(f(i) & "\*.pdf")
        If InStr(f(i), p2 & "\") = 0 And Dir(f(i) & "\*.pdf") <> "" Then
            'Need 2 pdfs to merge or pdfill slows and may error
            If Dir <> "" Then  'at least 2 pdfs files exist
                s = """" & "C:\Program Files (x86)\PlotSoft\PDFill\pdfill.exe" & """" & _
                    " MERGE " & _
                    """" & f(i) & "\" & """" & " " & _
                    """" & r & fso.GetFolder(f(i)).Name & ".pdf" & """"
                Shell s, vbHide
                Else
                FileCopy k, (r & fso.GetFolder(f(i)).Name & ".pdf")
            End If
        End If
    Next i
    Set fso = Nothing
    MsgBox "PDF files merged to folder: " & r
End Sub