Post Views: 5,466

Time from time, you may need to combine PDF file together. For example, you are not familiar with scanner and cannot scan multiple pages into one file. In this situation, you can scan documents one page by one page and then combine all scanned PDF file together. That’s just a kidding. 😊 In most cases, SAS programmers produce TFL outputs in PDF format and are required to combine separate PDF files into one PDF file. Most people would use “Combine Files into a Single PDF” feature from Adobe Acrobat and then combine files manually. This post would like to introduce you a VBA macro which can enable you to complete the task within several minutes. If you don’t know how to create VBA macro, please read this post first.

Introduction on VBA macro to combine PDF files

Here is how to macro looks like and show you where to place important information. Cell B1 is to contain folder where PDF files to combined are placed. Cell B2 is for folder where you want your combine PDF file to be placed. Cell B3 is to contain name of your combined PDF. Filenames in column D are filled by Excel automatically once you click on ‘Get File Name’ button. As soon as you click on “Combine PDF” button, Excel will combine all files together and place it in folder listed in cell B2.

VBA macro to retrieve File Name

Click here to hide/show code


Sub Getfname_Click()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

lastrow = ThisWorkbook.Worksheets(“Combine PDF”).Cells(Rows.Count, “D”).End(xlUp).Row

‘Clear content
For j = 2 To lastrow
Cells(j, 4) = “”
Next j

‘Create an instance of the FileSystemObject
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
‘Get the folder object
inpath = Trim(ThisWorkbook.Worksheets(“Combine PDF”).Cells(1, 2))
Set objFolder = objFSO.GetFolder(inpath)

i = 2
‘loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files

temp = Split(objFile.Name, “.”)
If temp(UBound(temp)) = “pdf” Then
‘print file name
Cells(i, 4) = temp(0)
i = i + 1
End If
Next objFile

MsgBox “Done!”

End Sub

VBA macro to combine PDF files

Click here to hide/show code


Sub Combine_Click()
Application.ScreenUpdating = False

Dim AcroApp As Acrobat.CAcroApp
Dim PDoc As Acrobat.CAcroPDDoc

Set AcroApp = CreateObject(“AcroExch.App”)
Set PDoc = CreateObject(“AcroExch.PDDoc”)

PDoc.Open (ThisWorkbook.Worksheets(“Combine PDF”).Cells(1, 2) & “\” & ThisWorkbook.Worksheets(“Combine PDF”).Cells(2, 4) & “.pdf”)
Set jso = PDoc.GetJSObject
Set BMR = jso.BookmarkRoot

Dim P2Doc As Acrobat.CAcroPDDoc
Dim numPages As Integer
pg = 0

‘Check missing info
For i = 2 To ActiveSheet.Range(“D” & Rows.Count).End(xlUp).Row
If ThisWorkbook.Worksheets(“Combine PDF”).Cells(i, 4) = “” Then
MsgBox “Row: ” & i & “,” & ThisWorkbook.Worksheets(“Combine PDF”).Cells(i, 4) & ” does not exist !”
Exit Sub
End If
Next

For i = 3 To ActiveSheet.Range(“D” & Rows.Count).End(xlUp).Row

Set P2Doc = CreateObject(“AcroExch.PDDoc”)
P2Doc.Open (ThisWorkbook.Worksheets(“Combine PDF”).Cells(1, 2) & “\” & ThisWorkbook.Worksheets(“Combine PDF”).Cells(i, 4) & “.pdf”)

‘Insert the pages of Part2 after the end of Part1
numPages = PDoc.GetNumPages()
pg = numPages

If PDoc.InsertPages(numPages – 1, P2Doc, 0, P2Doc.GetNumPages(), True) = False Then
MsgBox “Cannot insert pages” & ActiveSheet.Cells(i, 4) & “.pdf”
End If

P2Doc.Close

Next

If PDoc.Save(PDSaveFull, ThisWorkbook.Worksheets(“Combine PDF”).Cells(2, 2) & “\” & ThisWorkbook.Worksheets(“Combine PDF”).Cells(3, 2) & “.pdf”) = False Then
MsgBox “Cannot save the modified document”
End If

PDoc.Close

AcroApp.Exit
Set AcroApp = Nothing
Set PDoc = Nothing
Set ADoc = Nothing
Set P2Doc = Nothing

MsgBox “Done”

End Sub

Note:

This macro only works in a computer when Abode Acrobat Pro is available. Abode Reader is not enough and just dose not work. One more thing is that you have to click on Tools -> References to open Reference dialog box and then tick all necessary references showed in following picture.