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).

 

[jimmied]

Okay Dan, thanks very much for the information. Much appreciated.

 

[Dan_W]

Failing that, there is a solution using VBA and Access over here: Access Modern Web Browser Control – Merging PDFs | DEVelopers HUT
It leverages the new WebView2 control available in Access. Essentially it uses VBA to control Javascript, which is what is used to combine the PDF files. It might be possible to use the same method using the WebBrowser control in Excel/Word/Powerpoint, but I've not tried it. Just a thought.

 

[alansidman]

Here is a simple way to achieve your needs

 

[Dan_W]

Here is a simple way to achieve your needs

Does it? My understanding of OP / the VBA code is that goal is to combine a number of PDF files into a single PDF file.

 

[alansidman]

Using Power Query to achieve the end result is an alternative to VBA. There is no requirement for the OP to use it. It is an opportunity to learn about PQ and how it often easier to do things like consolidation of PDF files with out coding.

 

[Dan_W]

I legitimately don't understand. This imports PDF files into Excel, bulk importing them into a single Excel workbook. OP asks about combining PDF files into a single PDF file. No?

 

[Dan_W]

I've had another thought, @jimmied - You could open the PDF files in Word, then stitch them together into a single Word document, then output that Word Document as a PDF file. The problem with that approach, though, is that there is no guarantee that the formatting would be retained...

 

[alansidman]

@Dan W
Once the PDF files have been merged into an Excel file, it can then be saved as a PDF. Same concept as you prescribe in #9 with a word doc. I thought that this step would be intuitive to any one with advanced Excel skills which the OP has appeared to show.