Results 1 to 6 of 6

Thread: Append to pdf VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular diddi's Avatar
    Join Date
    May 2004
    Location
    Shepparton, Australia
    Posts
    2,554
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Append to pdf VBA

    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
    The more you learn, the more you realise how little you know. Excel 2003 and 2010 / Win7
    Code:
    Sub HintForGoodResults( )
        If yourcode Is indented Then
            "it is easier to for everyone to understand" AND "others are more likely to give assistance"
        Else
            "errors are much harder to find" OR "others dont bother"
        End If
    End Sub

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append to pdf VBA

    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-...using-vba.html

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

  3. #3
    Board Regular diddi's Avatar
    Join Date
    May 2004
    Location
    Shepparton, Australia
    Posts
    2,554
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append to pdf VBA

    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.
    The more you learn, the more you realise how little you know. Excel 2003 and 2010 / Win7
    Code:
    Sub HintForGoodResults( )
        If yourcode Is indented Then
            "it is easier to for everyone to understand" AND "others are more likely to give assistance"
        Else
            "errors are much harder to find" OR "others dont bother"
        End If
    End Sub

  4. #4
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,429
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Append to pdf VBA

    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 by lrobbo314; Aug 19th, 2019 at 12:32 AM.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  5. #5
    Board Regular diddi's Avatar
    Join Date
    May 2004
    Location
    Shepparton, Australia
    Posts
    2,554
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append to pdf VBA

    thx for that. i will have another go
    The more you learn, the more you realise how little you know. Excel 2003 and 2010 / Win7
    Code:
    Sub HintForGoodResults( )
        If yourcode Is indented Then
            "it is easier to for everyone to understand" AND "others are more likely to give assistance"
        Else
            "errors are much harder to find" OR "others dont bother"
        End If
    End Sub

  6. #6
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append to pdf VBA

    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 by Kenneth Hobson; Oct 20th, 2019 at 07:37 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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