Append to pdf VBA

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
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
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,498
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.

Code:
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')
 
Last edited:

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,082
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.
Code:
'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.
Code:
'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.
Code:
'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
 
Last edited:

Forum statistics

Threads
1,078,148
Messages
5,338,522
Members
399,240
Latest member
mominul2241

Some videos you may like

This Week's Hot Topics

Top