Combine PDFs using VBA

Dash12

Board Regular
Joined
Jun 17, 2015
Messages
125
Hi,
I have a workbook in which I save 3 worksheets as a PDF and print them out. Now, one of the actual excel sheets triggers an external pdf file. I need to combine this external PDF to the other 3 PDFs created before. I have tried few different way but to no avail. Any suggestions would be appreciated
Thank you
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
some code I use. i only need to handle small numbers of files; if you have lots, it'll need a tweak I think

https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/

Code:
Sub pdf_joiner()
  '
  ' Accepts list of source pdf files, then combines all to a new file.
  ' New file in same sub-directory; new file named "yyyy-mm-dd hhmmss.pdf"
  '
  ' Original files are unchanged. So if something goes wrong in execution, it doesn't really matter
  '
  Const bln_DEBUG_MODE As Boolean = False
  Const str_NAME_OF_EXE As String = "PDFtk"


  Dim i As Long
  Dim strOutput As String
  Dim strPath As String
  Dim varInput As Variant


  varInput = Application.GetOpenFilename(FileFilter:="Pdf files (*.pdf),*.pdf", Title:="Pick the files to join", MultiSelect:=True)
  If Not IsArray(varInput) Then Exit Sub


  strPath = Left$(varInput(1), InStrRev(varInput(1), "\"))  'Save new file in same path as source files


  strOutput = str_NAME_OF_EXE
  For i = LBound(varInput) To UBound(varInput)
    strOutput = strOutput & " " & Chr(64 + i) & "=""" & varInput(i) & """"
  Next i
  Erase varInput
  strOutput = strOutput & " OUTPUT """ & strPath & Format$(Now, "yyyy-mm-dd hhmmss") & ".pdf"""


  Debug.Print vbCr & "PDF JOINER " & Now & Join$(varInput) & vbCr & strOutput
  
  If bln_DEBUG_MODE Then
    MsgBox prompt:=strOutput, Buttons:=vbOKOnly, Title:="PDF Joiner"
  Else
    Call Shell(strOutput, vbNormalFocus)
  End If


End Sub

btw, I have code for other operations too
 
Upvote 0
Hi Fazza,
There is precious little out there on using vba for pdf manipulation, Id be interested in your other codes.

I have pdfcreator v1 and need to be able to merge a specific page from 1 pdf into a specific place in a second pdf.

Alternatively I need to be able to print a specific page from an external pdf.
I'd be very interested if you would like to share what other operations you have managed to code.
Thanks
 
Upvote 0
As Jerry said, a 3rd party program is needed to merge/combine existing PDF files. The methods to use those from VBA differs depending on the version and their capabilities. PDFSam is one that I like to use in a Shell() process. PDFCreator is probably better in v1 than v2.1. Below I will show a link that has v1 example code and my code which is very slow due to long lags in dialog dismissal for v2.1. Of course you can manually dismiss those dialogs but it is a pain if you do many.

Of course if you have Adobe Acrobat, not Reader, you can use VBA to do it.

Code:
'http://www.excelguru.ca/forums/showthread.php?4733-merging-multiple-PDF-files-into-a-single-PDF-file-via-VBA-macro
Sub Test_PDFCreatorCombine()
  Dim fn(0 To 1) As String, s As String
  fn(0) = "x:\pdf\ken.pdf"
  fn(1) = "x:\pdf\ken2.pdf"
  s = "x:\pdf\PDFCreatorCombined.pdf"
  PDFCreatorCombine fn(), s
      
  If vbYes = MsgBox(s, vbYesNo + vbQuestion, "Open?") Then Shell ("cmd /c " & """" & s & """")
End Sub

'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' COM interface, http://www.pdfforge.org/pdfcreator/manual/com-interface
' Designed for early bind, set reference to: PDFCreator - Your OpenSource PDF Solution
Sub PDFCreatorCombine(sPDFName() As String, sMergedPDFname As String)
  Dim oPDF As PDFCreator.PdfCreatorObj, q As PDFCreator.Queue
  Dim pj As PrintJob
  Dim v As Variant, i As Integer
  Dim fso As Object
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  
  Set q = New PDFCreator.Queue
  With q
    .Initialize
    If LBound(sPDFName) = 0 Then
      .WaitForJobs UBound(sPDFName) + 1, 1
      Else
      .WaitForJobs UBound(sPDFName), 1
    End If
  
    
    Set oPDF = New PDFCreator.PdfCreatorObj  'PDFCreator.clsPDFCreator
  
    i = 0
    For Each v In sPDFName()
      If fso.FileExists(v) Then oPDF.PrintFile v
      i = i + 1
    Next v
    .MergeAllJobs
    
    Set pj = q.NextJob
    With pj
      .SetProfileByGuid "DefaultGuid"
      .SetProfileSetting "Printing.PrinterName", "PDFCreator"
      .SetProfileSetting "Printing.SelectPrinter", "SelectedPrinter"
      .SetProfileSetting "OpenViewer", "false"
      .SetProfileSetting "OpenWithPdfArchitect", "false"
      .SetProfileSetting "ShowProgress", "false"
      .ConvertTo sMergedPDFname
    End With
  
    .ReleaseCom
  End With
End Sub


Hi Ken,

I tried your code but an issue crops up: Run time error '429' ActiveX component can't create object. The line that gets highlighted is:
Code:
Set q = New PDFCreator.Queue

I wonder if you may be able to help?

I am needing the merged file to be in a specific order of pages but that is a different matter entirely.
 
Upvote 0
Hello everyone,

Sorry for re-opening this thread I have a quick question in regards using PDFsam through VBA to split and merge PDF's

I've followed on from the code Ken supplied and added anything that was required to output what I needed (splitting at page 6).

I keep coming up with an error which states "Error executing ConsoleClient java.lang.Exception: org.pdfsame.console.exceptions.console......."

The code im using is as follows:

Sub split()
Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
Dim pdfOut As String, q As String
q = """"
pdfsam = "cmd /k java -Dlog4j.configuration=console-log4j.xml -jar " & q & _
"C:\Program Files\PDF Split And Merge Basic\lib\" & _
"pdfsam-console-2.4.3e.jar" & q
pdfFiles = "-f " & q & "H:\KLE\JCB - Templates\10374052-A10.pdf" & q
pdfOut = "-o " & q & "H:\KLE\JCB - Templates\Temp\" & q
pdfsamStr = pdfsam & " " & pdfFiles & " -p SamDoc -s SPLIT -n 6 " & pdfOut & " split"
Debug.Print pdfsamStr
Shell pdfsamStr, vbNormalFocus
End Sub

If someone could give me any indication of what might be going wrong it would be extremely helpful as my VBA coding experience is only very minor.

Cheers

John
 
Upvote 0
Welcome to the forum!

It might have been best to start your own thread and reference this one. When pasting code, paste between code tags. Insert them by clicking the # icon on the reply toolbar.

Copy your pdfsamStr from the Immediate Window after a run. You might be able to see what is wrong from the string. Paste it into a Run window (Win+R) or paste from a CMD shell. To open the shell, type CMD and press Enter in a Run dialog (Win+R).

If still stuck, paste the string here. Obviously, we don't know if paths are correct but we can look at syntax. One can always add error routines to check for paths.
 
Last edited:
Upvote 0
Hi Ken,

Thanks for the pointers, I managed to find a solution the error I was receiving was due to some incorrect syntax.

Cheers
 
Upvote 0
Hi,

i am new to the forum and been following threads on merging and splitting pdf files in excel using pdfsam. I'm a beginner in VBA's but i wish to learn how can merge multiple pdf files that i have generated form macro in my excel. so just to try out the split i used the the coding below:

Code:
Sub split()
    Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
    Dim pdfOut As String, q As String
    q = """"
    pdfsam = "cmd /k java -Dlog4j.configuration=console-log4j.xml -jar " & q & _
    "C:\Program Files\pdfsam\lib\" & _
    "pdfsam-console-2.4.3e.jar" & q
    pdfFiles = "-f D:\New\1.pdf" & q
    pdfOut = "-o D:\New\Temp\" & q
    pdfsamStr = pdfsam & " " & pdfFiles & " " & pdfOut & " -p test.pdf" & " -s SPLIT"
    Debug.Print pdfsamStr
    Shell pdfsamStr, vbNormalFocus
End Sub

but it ends up with error below. similar to Jmarro's but it seems he manage to solve the but missed to share it here. i hope you may be able to help to assist me. my ultimate is merge all the pdf files generated in a folder and save it as a single file.

the error
Code:
15:04:02,722 DEBUG  Starting arguments parsing.
15:04:02,831 FATAL  Error executing ConsoleClient
java.lang.Exception: org.pdfsam.console.exceptions.console.ParseException: PRS00
1 - Parse error. Option <f> is not a valid option.
        at org.pdfsam.console.business.ConsoleServicesFacade.parseAndValidate(Co
nsoleServicesFacade.java:85)
        at org.pdfsam.console.ConsoleClient.main(ConsoleClient.java:37)
Caused by: org.pdfsam.console.exceptions.console.ParseException: PRS001 - Parse
error. Option <f> is not a valid option.
        at org.pdfsam.console.business.parser.CmdParseManager.parse(CmdParseMana
ger.java:118)
        at org.pdfsam.console.business.parser.CmdParseManager.parse(CmdParseMana
ger.java:137)
        at org.pdfsam.console.business.ConsoleServicesFacade.parseAndValidate(Co
nsoleServicesFacade.java:78)
        ... 1 more

C:\Users\Richard\Documents>

thanks in advance
 
Upvote 0
I'm sorry, i have posted my trial codes.. these are the one

the code
Code:
Sub split()
    Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
    Dim pdfOut As String, q As String
    q = """"
    pdfsam = "cmd /k java -Dlog4j.configuration=console-log4j.xml -jar " & q & _
    "C:\Program Files\PDF Split And Merge Basic\lib\" & _
    "pdfsam-console-2.4.1e.jar" & q
    pdfFiles = "-f " & q & "D:\1.pdf" & q
    pdfOut = "-o " & q & "D:\New\Temp\" & q
    pdfsamStr = pdfsam & " -p SamDoc -s BURST " & pdfFiles & " " & pdfOut & " SPLIT"
    Debug.Print pdfsamStr
    Shell pdfsamStr, vbNormalFocus
End Sub

the error, no matter how i change them, they always show that Option <p> is not a valid option, Option <f> is not a valid option or Option <o> is not a valid option.

Code:
16:08:56,190 DEBUG  Console LOG level set to DEBUG
16:08:56,221 DEBUG  Starting arguments parsing.
16:08:56,362 DEBUG  Command 'D:\New\Temp" SPLIT' parsed in 0 seconds
16:08:56,362 FATAL  Error executing ConsoleClient
java.lang.Exception: org.pdfsam.console.exceptions.console.ParseException: PRS00
1 - Parse error. Option <p> is not a valid option.
        at org.pdfsam.console.business.ConsoleServicesFacade.parseAndValidate(Co
nsoleServicesFacade.java:96)
        at org.pdfsam.console.ConsoleClient.main(ConsoleClient.java:79)
Caused by: org.pdfsam.console.exceptions.console.ParseException: PRS001 - Parse
error. Option <p> is not a valid option.
        at org.pdfsam.console.business.parser.CmdParseManager.parse(CmdParseMana
ger.java:188)
        at org.pdfsam.console.business.parser.CmdParseManager.parse(CmdParseMana
ger.java:211)
        at org.pdfsam.console.business.ConsoleServicesFacade.parseAndValidate(Co
nsoleServicesFacade.java:89)
        ... 1 more

C:\Users\Richard\Documents>
 
Upvote 0

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top