Results 1 to 15 of 15
  1. #1
    Kody_Devl is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    22

    Access VBA to Merge two (or more) PDFs into 1 new file

    Hi All,



    I need to be able to merge existing PDF files into 1 new file that has all of the documents in it.

    For example:
    Merge pdf1 + pdf2 +Pdf3 + pdf4..........................................Up to 150 pdfs------------------> New Single pdf (very large).

    I have Adobe Reader DC (but not Adobe Exchange).

    Does anyone have a vba procedure to do this. The code to loop through docs is not a problem. I just need the "Merge" function.

    PDF1-------> Merge(PDF2)---------->PDF_Combine.pdf


    Many Thanks

    Kody_Devl

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm not sure this is possible without using Adobe Pro versions.
    Can you do it externally with the Reader DC? If not, no amount of VBA will get around that restriction.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please review this thread, should put you on the right track:

    https://www.access-programmers.co.uk.../#post-1744564

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    ctonline is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    10
    I use the Adobe DC Pro version and it allows for combination of PDF files into one large file. The regular version does not have this feature.

    Adobe DC Pro also allows for typed text recognition feature for searchable text in pdf. The other useful features with the DC pro is also to be able to create fillable pdf forms and conversion of PDF to words and other format and to convert other formats into pdf file.

  5. #5
    Kody_Devl is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    22
    Fantastic,

    Do you merge files using MS Access? Do you have any vba that would do this that I could borrow. If Access, do you know which library is requied?

    Thanks,

    Kody_Devl

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you have the Pro version this post describes adding pages to a existing pdf

    https://www.access-programmers.co.uk.../#post-1744247

    However, that wasn't what you said in the original post. This won't work on machines without Acrobat Pro installed.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Kody_Devl is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    22
    Hi GICU

    This thread did get me to some working code that merges PDFs beautifully except, that if a PDF is password protected, it "Fails of merge" that document.

    Thank you as you have gotten me almost there. Now I have to find and pass a password, somehow.

    Kody_Devl

  8. #8
    Shadow9449 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Feb 2017
    Posts
    38
    If someone wants a solution that doesn't require Adobe Pro, Lebans had a report to PDF solution a long time ago that while may be obsolete in the export function, still can be helpful in merging PDFs.

    Here's the link:

    https://www.lebans.com/reporttopdf.htm

    Here's some fast code I use to access it:
    Code:
          sMaster = "C:\[path to master].PDF"
          sChild = "C:\[path to child].PDF"
    
    
          blRet = MergePDFDocuments(sMaster, sChild)
    I hope this helps someone.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    To add to that I've happily been using the free software PDFtk for years!

    Here's an example shell command to merge two pdfs.
    Code:
    pdftk doc1.pdf doc2.pdf output combined.pdf
    https://www.pdflabs.com/docs/pdftk-man-page/

    Here's a little helper sub I use to send stuff from vba to the command line
    Code:
    'this sub will execute a shell command and wait for execution to finish
    Public Sub RunCmd(cmd As String)
    On Error GoTo ErrHandler_RunCmd
        'https://stackoverflow.com/questions/15951837/wait-for-shell-command-to-complete
        Dim wsh As Object
        Set wsh = VBA.CreateObject("WScript.Shell")
        Dim waitOnReturn As Boolean: waitOnReturn = True
        Dim windowStyle As Integer: windowStyle = 1
        'Debug.Print cmd
        wsh.Run cmd, windowStyle, waitOnReturn
        
    ExitHandler_RunCmd:
        Set wsh = Nothing
        Exit Sub
        
    ErrHandler_RunCmd:
        Resume ExitHandler_RunCmd
    End Sub

    EDIT

    AND here is the bare bones beginning of how I might go about writing a module of subs to handle calls to the pdftk program. Good luck!
    Code:
    Option Compare Database
    Option Explicit
    
    'https://www.pdflabs.com/docs/pdftk-man-page/
    Const PDFtkPath As String = "C:\Program Files (x86)\PDFtk\bin\pdftk.exe"
    
    'DON'T FORGET TO PUT QUOTATION MARKS AROUND FILE PATHS BEFORE SENDING TO COMMAND LINE
    
    'generic base function to call pdftk from the command line
    Public Sub PDFtk(args As String)
        Dim cmd As String
        cmd = """" & PDFtkPath & """ " & args
        'Debug.Print cmd
        RunCmd cmd
    End Sub
    
    'function to merge two pdf files
    'should probably do some error check here to make sure input files exist and
    'output file doesn't already exist
    Public Sub PDFtkMerge(f1 As String, f2 As String, output As String)
        Dim args As String
        args = """" & f1 & """ """ & f2 & """ output """ & output
        PDFtk args
    End Sub

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Since I also happen to have need for this functionality and have been putting it off I went ahead and fleshed this out a little more. I wrote a function PDFtkMerge that accepts an array of filenames and an output file name and merges them. Again, this requires PDFtk to be installed in the user's computer (or more specifically PDFtk Server, the command line tool). Make sure the executable path at the top of the module is correct. I've collected all the code necessary to make this work in a single module file for you. At the very bottom of the file you'll find a test sub that demonstrates the usage of the merge function:

    Code:
    Option Compare Database
    Option Explicit
    
    'https://www.pdflabs.com/docs/pdftk-man-page/
    Const PDFtkPath As String = "C:\Program Files (x86)\PDFtk\bin\pdftk.exe"
    
    Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
        'Purpose:   Return True if the file exists, even if it is hidden.
        'Arguments: strFile: File name to look for. Current directory searched if no path included.
        '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
        'Note:      Does not look inside subdirectories for the file.
        'Author:    Allen Browne. http://allenbrowne.com June, 2006.
        Dim lngAttributes As Long
    
        'Include read-only files, hidden files, system files.
        lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
    
        If bFindFolders Then
            lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
        Else
            'Strip any trailing slash, so Dir does not look inside the folder.
            Do While Right$(strFile, 1) = "\"
                strFile = Left$(strFile, Len(strFile) - 1)
            Loop
        End If
    
        'If Dir() returns something, the file exists.
        On Error Resume Next
        FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
    End Function
    
    Function FolderExists(strPath As String) As Boolean
        On Error Resume Next
        FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
    End Function
    
    Function TrailingSlash(varIn As Variant) As String
        If Len(varIn) > 0 Then
            If Right(varIn, 1) = "\" Then
                TrailingSlash = varIn
            Else
                TrailingSlash = varIn & "\"
            End If
        End If
    End Function
    
    
    'this sub will execute a shell command and wait for execution to finish
    Public Sub RunCmd(cmd As String)
    On Error GoTo ErrHandler_RunCmd
        'https://stackoverflow.com/questions/15951837/wait-for-shell-command-to-complete
        Dim wsh As Object
        Set wsh = VBA.CreateObject("WScript.Shell")
        Dim waitOnReturn As Boolean: waitOnReturn = True
        Dim windowStyle As Integer: windowStyle = 1
        'Debug.Print cmd
        wsh.Run cmd, windowStyle, waitOnReturn
        
    ExitHandler_RunCmd:
        Set wsh = Nothing
        Exit Sub
        
    ErrHandler_RunCmd:
        Resume ExitHandler_RunCmd
    End Sub
    
    
    'base function to call pdftk from the command line
    Public Sub PDFtk(args As String)
        Dim cmd As String
        cmd = """" & PDFtkPath & """ " & args
        RunCmd cmd
    End Sub
    
    'PDFtkMerge will combine an array of pdf files into a single output file
    ' it will fail of any of the input files are not found or of the output
    ' filename already exists.
    ' returns True of a new output file is created, otherwise returns false
    Public Function PDFtkMerge(files() As Variant, output As String) As Boolean
    On Error GoTo ErrHandler_PDFtkMerge
        Dim filename As Variant
        Dim rslt As Boolean
        Dim args As String
        
        rslt = False
        
        'loop through the files and make surey the exist
        For Each filename In files
            If Not FileExists(filename) Then Err.Raise 1001, , "Input file not found" & vbCrLf & vbCrLf & filename
            args = args & """" & filename & """ "
        Next filename
        
        'check the output filename
        If FileExists(output) Then Err.Raise 1002, , "Output file already exists" & vbCrLf & vbCrLf & output
        args = args & "output """ & output & """"
        
        'run the command
        'Debug.Print args
        PDFtk args
        
        'see if it worked
        If FileExists(output) Then rslt = True
        
    ExitHandler_PDFtkMerge:
        PDFtkMerge = rslt
        Exit Function
        
    ErrHandler_PDFtkMerge:
        MsgBox Err.Description, , "PDFtkMerge Error #" & Err.Number
        Resume ExitHandler_PDFtkMerge
    
    End Function
    
    
    Public Sub MERGE_TEST()
        Dim input_files() As Variant
        Dim output_file As String
        
        input_files = Array("D:\S1.10.pdf", _
                            "D:\S2.20.pdf", _
                            "D:\S2.30.pdf", _
                            "D:\S3.10.pdf")
                            
        output_file = "D:\s_combined.pdf"
        
        Debug.Print PDFtkMerge(input_files, output_file)
    End Sub
    *I said free but double check the licensing terms for your situation.
    ** also, the docs appear to show it might be able to handle your files with passwords. We'll need to modify the code above for that...

  11. #11
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    PDF files may be merged using Power Query which is part of Excel. Once merged they can then be linked/imported into Access for whatever analysis is required. This does require Office 365 for this functionality

    https://www.google.com/search?q=Powe...iO9PwPr8KDQA22

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Danpeterson View Post
    In order to delete a too-long-file, all you need to do is open a command prompt in the directory where the file is located and use a simple command to get the short file name. Open File Explorer and navigate to the directory where your files are located. Press and hold Shift, then right-click on an empty area. Choose “Open command window here”. A Command Prompt window will open, focused on the directory you’re in.
    Yet you do not say what the command is that you have to use?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    sxschech is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    5
    Quote Originally Posted by Welshgasman View Post
    Yet you do not say what the command is that you have to use?
    What about this?

    Code:
    Option Compare DatabaseOption Explicit
    
    
    Private Declare PtrSafe Function GetShortPathName Lib "kernel32" _
       Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
       ByVal lpszShortPath As String, ByVal cchBuffer As Long) _
       As Long
    
    
    Public Function GetShortFileName(ByVal FullPath As String) As String
    'PURPOSE: Returns DOS File Name (8.3 Format) Give
    'FullPath for long file name
    
    
    'PARAMETERS: FullPath: Full Path of Original File
    
    
    'RETURNS: 8.3 FileName, or "" if FullPath doesn't
    '         exist or file fails for other reasons
    
    
    'EXAMPLE:
    'Debug.Print _
    '  GetShortFileName("C:\My Documents\My Very Long File Name.doc")
    
    
    'If file exists, will display C:\MYDOCU~1\MYVERY~1.DOC
    'in the debug window
    'https://www.mrexcel.com/forum/excel-questions/401394-code-convert-path-name-short-dos-style.html
    '20190807
    Dim lAns As Long
    Dim sAns As String
    Dim iLen As Integer
       
    On Error Resume Next
    
    
    'this function doesn't work if the file doesn't exist
    If Dir(FullPath) = "" Then Exit Function
    
    
    sAns = Space(255)
    lAns = GetShortPathName(FullPath, sAns, 255)
    GetShortFileName = Left(sAns, lAns)
        
    End Function

  14. #14
    ijs777 is offline Novice
    Windows 10 Access 2019
    Join Date
    Oct 2022
    Posts
    2
    Thanks so much. I could not believe it when that worked, ha ha. I get so much help from these places I really need to start giving back

  15. #15
    ijs777 is offline Novice
    Windows 10 Access 2019
    Join Date
    Oct 2022
    Posts
    2
    So, thanks to KD2017, a very very simplified bit of code would be this (once you have installed PDFtk);

    Shell ("C:\Program Files (x86)\PDFtk\bin\pdftk.exe C:\test1.pdf C:\test2.pdf Output C:\Comb.pdf")

    So have your pdfs test1, test2 sat in your C drive. Cut and paste this code and it will create the combined file "comb.pdf" in the same directory as above.
    Now you can get fancy and set up a procedure, create variables and make it more professional - but this breaks it down for a beginner.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Report to multiple PDFs FILES
    By vianda in forum Reports
    Replies: 3
    Last Post: 08-11-2020, 08:18 PM
  2. Attach Multiple PDFs to Access via Script
    By dimitrius20 in forum Access
    Replies: 2
    Last Post: 03-06-2015, 02:31 PM
  3. Replies: 2
    Last Post: 10-02-2012, 09:54 PM
  4. Print linked pdfs within an Access Report II
    By cjwagner in forum Reports
    Replies: 3
    Last Post: 05-30-2011, 04:25 PM
  5. Print linked pdfs within an Access Report
    By alpruett in forum Reports
    Replies: 6
    Last Post: 05-20-2011, 05:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums