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.
Dear author,
Really nice and helpful post but my scenarios are quite different and difficult than this like –
1) we want to combine all rtf files of summaries and listing into on single rtf file.
2) this file should have new page numbers and individual page numbers should not come.
And 3) create an combined pdf file from combined etc file.
I have explored many SAS papers on web and can able combine all o/ps in to single etc file but issue is that program can’t differentiate between landscape / portrait mode. It can give either of both.
Can you please suggest any method to achieve this task?
Thanks in advance!
~ Nikhil
Sure, I will write a simple paper later.
can you also help in how to set up the call buttons, once the VBA script is added to the excel.
thanks,
You can read this post http://pharma-sas.com/how-to-retrieve-comments-from-word-document-using-vba/. It has detailed instructions. Please let me know if you still have issues.
Click on Developer tab -> Insert and then select the first one – button. After a plus sign appears, you can move the plus sign and draw a box which is a button. After you press up your mouse, Assign Macro dialog box will be showed up.