Merge all pdf files from a folder into single PDF using VBA

[jimmied]

Good afternoon

I am looking for help with the below code please, as although it works insofar as it creates the file and appears to attach all eighty pdf's due to the fact that the files in the folder total 28MB, and the created file is 28MB, however, when I open the created file, it only has one of the eighty pdf's showing

I cannot work out why it only shows one of the pdf's instead of all of them, and so any help would be appreciated please.

Many Thanks
Jimmie

Sub JoinFiles()

Dim StrPath As String, FileName As String, FileExt As String
FileName = "Name It" ' New File name <<<<<
FileExt = ".pdf"
 StrPath = Environ("USERPROFILE") & "\Desktop\pdf Folder" ' replace with Your Folder Path
 
Call JoinFiles_In_One(StrPath, FileName, FileExt)

End Sub

Public Sub JoinFiles_In_One(FlderPath As String, FileName As String, FileExt As String)

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim SourceFile As String, TargetFile As String
StrPath = FlderPath
PathArr = Split(StrPath, "\")
UstrPath = UBound(PathArr)
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(StrPath)
    ' List Files
    N = 0
    SourceFile = ""
    TargetFile = ""
    For Each oFile In oFolder.Files
        If oFile.Name <> FileName & FileExt Then
            Open FlderPath & "\" & oFile.Name For Binary As #1
            N = N + 1
            SourceFile = Right(FileExt, Len(FileExt) - 1) & "_" & N
            SourceFile = String(LOF(1), 0)
            
            Get #1, 1, SourceFile
            Close #1
        TargetFile = TargetFile & SourceFile
       End If
    Next oFile
     'Mergge  File
         Open FlderPath & "\" & FileName & FileExt For Binary As #1
         Put #1, 1, TargetFile
         Close #1

End Sub

 

[Dan_W]

PDF file structures are such that it isn't simply a matter of just combing the data from each file into a single file. That won't work. The reason it will only show the first PDF file of the bunch (though I'm a little surprised it even does that), is because data in the file you're creating is only referencing data for the first PDF file - effectively ignoring the balance of it. There are a number of experts on this site that have produced working solutions for PDF manipulation - though it will require installation of additional software (PDFTK, I think it's called).

Here is the post I was thinking of: VBA Export diferent excel files to pdf and then combine them to one file
Alternatively, if you have PDF files with tables of data that want to combine into a single file then PowerQuery may be your best option.