merging multiple PDF files into a single PDF file via VBA macro

Telanious

New member
Joined
Jul 23, 2015
Messages
6
Reaction score
0
Points
0
I have an excel file in which I have a macro that creates a series of PDF files via the ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ command.

I end up with files such as main file, temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8 and temp9. What I would like to do now is combine these files into a single file with the same name as the main file. I am attempting to do this with PDFCreator version 1. I can do it just fine manually, but I would like to be able to do it via the same macro that creates the files. So far, I have added PDFCreator as a reference in the tools, reference in VBA. I then go into object browser and can see the diffent objects that are available, but none of them have a description of what they do so I am at a loss at to which commands will help with the task I am attempting. (If anyone knows of a resource that explains the objects I would appreciate it if you point me in that direction. A goggle search has come up empty for me so far.)

What I am in need of help with is this:

Either help creating a macro that loads the existing files into PDFCreator, combines them all into one file, then outputs them as a PDF file with the same name as the main file

or

help creating a macro that gets rid of the command I am using and instead sends the files to the PDFCreator print queue and then combines them and outputs them with the selected name.

Thank you in advance!
 
Simplest thing to see what the required code would be is to use the macro recorder.
Select all the sheets and save as pdf.
 
Are you married to a PDFCreator solution or just a way to accomplish your goals?

IF the later, then something like this can be done as NoS said. This example does a bit more as it orders the sheets so that the PDF file is created in a specify way.

I don't know what you mean by a selected name.
Code:
Sub CreatePDF2()  Dim ws As Worksheet


  Set ws = ActiveSheet


  If ThisWorkbook.Path = "" Then
    MsgBox "Save File First"
    Exit Sub
  End If
  
  'Order worksheets for proper pdf creation in order
  Worksheets("Customer Info").Move after:=Worksheets(Worksheets.Count)
  Worksheets("Order Info").Move after:=Worksheets(Worksheets.Count)
   
  With Sheets("Order Info")
    Worksheets(Array("Customer Info", "Order Info")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=ThisWorkbook.Path & "\CustomerOrderInfo.pdf", _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
     
  ws.Select
End Sub
 
As I stated in my original post, I am not married to a PDFCreator solution, but as I also stated I am working with PDF files. I have a worksheet that has a drop down selector. Based on the selection the data in a number of graphs on that sheet changes. The macro I have created makes a selection, then creates a PDF file of the sheet. Several selections comprise a region, so I am trying to merge the PDF files that are created into one PDF file. So, I end up creating PDF files like this:

Main PDF - Worksheet 1 with overall data
Temp1 PDF - Worksheet 2 with data for area 1
Temp2 PDF - Worksheet 2 with data for area 2
etc...

I then want to create a merged PDF file that has the same name as the Main PDF, but merges Main PDF, Temp1 PDF, Temp2 PDF, etc.

Due to the fact that I have to make a selection for each PDF file I can not load them into an array and export them as shown in your code.
 
Of course your macro could create the worksheets, use the method that I posted, and then remove the scratch worksheets.

I use Adobe Acrobat, not Reader, to do that at work. Since I don't have that at home, I use a 3rd party program like pdfsam to do that.

I don't know your level VBA experience. If you can't figure it out from link 181, post back and I will download pdfcreator and work up a more specific solution for you using pdfcreator.

I had to insert spaces in the url since I don't have 5 posts on this forum yet. www .excelguru .ca/content .php?181
 
Last edited:
Ok, I was finally able to modify the example code to get it to do what I wanted, however it hangs at this point every time I run it:

'Wait until the file shows up before closing PDF Creator
Do
DoEvents
Loop Until Dir(sPDFPath & sPDFName) = sPDFName

The file is created correctly and I can see it is in that path with that file name but it seems that the macro never sees it there so it never stops looping.
 
If you could paste, the code, it would help us help you. Paste between code tags. Click the Go Advanced button in lower right of a reply, and then the # icon on the toolbar or (code)MsgBox "test"(/code) but replace ()'s with []'s.

I am guessing that you may have used UNC paths. Dir() does not work with UNC. Scripting.FileSystemObject() (FSO) methods accepts UNC paths.

What version of PDFCreator are you using? I downloaded v2.1. One has to be careful getting the right version. Even then, be careful that you don't download a trojan.

PDFCreator.clsPDFCreator was not created in the registry by my version. The JScript help files from the installation did not work for me. This needs more work as it is annoying right now. It opens the PDF viewer(s) which one has to close manually for it to continue. In any case, here it is if you or others to play with by changing the settings to not open those applications and not show the final dialog where it creates the merged file.

Maybe Ken Puls can stop by and fix those issues.
Code:
Sub Test_PDFCreatorCombine()  Dim fn(0 To 1) As String
  fn(0) = "x:\pdf\ken.pdf"
  fn(1) = "x:\pdf\ken2.pdf"
  PDFCreatorCombine fn(), "x:\pdf\PDFCreatorCombined.pdf"
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
  
  'On Error GoTo EndNow
  
  Set q = New PDFCreator.Queue
  q.Initialize
  If LBound(sPDFName) = 0 Then
    q.WaitForJobs UBound(sPDFName) + 1, 1
    Else
    q.WaitForJobs UBound(sPDFName), 1
  End If
  
  Set oPDF = New PDFCreator.PdfCreatorObj  'PDFCreator.clsPDFCreator
  'Set pj = q.NextJob


  i = 0
  For Each v In sPDFName()
    oPDF.PrintFile v
    i = i + 1
    'Wait print job has entered the print queue
    'Do Until pj.GetOutputFiles.Count = i
    '    DoEvents
    'Loop
    'Debug.Print pj.IsFinished
    'Set pj = q.NextJob
    
    'pj.SetProfileByGuid ("DefaultGuid")
  Next v
  
  q.MergeAllJobs
  Set pj = q.NextJob
  pj.SetProfileByGuid ("DefaultGuid")
  pj.ConvertTo sMergedPDFname
EndNow:
  q.ReleaseCom
End Sub
 
I am using PDFCreator version 1. The company doesn't allow us to download and install software, so I am unable to update to the newest version at this time.

Here is the code I ended up with.

Code:
Sub CreateWestPDF()
Dim pdfjob As Object
Dim outputPDFName As String
Dim outputPDFPath As String
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

' Check to see if PDFCreator can start normally.
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
    MsgBox "Can't initialize PDFCreator.", vbCritical + _
        vbOKOnly, "Error!"
    Exit Sub
End If

pdfjob.cPrinterStop = True

'/// Change the output file name here! ///
outputPDFName = "West Area with Regions Week " & Sheets("Instructions_Input").Range("D5").Value & ".PDF"
outputPDFPath = ActiveWorkbook.Path & Application.PathSeparator

' set the defaults for the print jobs
With pdfjob
    .cOption("UseAutosave") = 1
    .cOption("UseAutosaveDirectory") = 1
    .cOption("AutosaveDirectory") = outputPDFPath
    .cOption("AutosaveFilename") = outputPDFName
    .cOption("AutosaveFormat") = 0    ' 0 = PDF
    .cClearCache
End With

Sheets("West Area").Select
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
Sheets("Regional").Select

i = 1
Range("C1").Value = i
Do While Range("C1") < 10
    Sheets("Regional").Select
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
    i = i + 1
    Range("C1").Value = i
Loop

' Wait until all print jobs have entered the print queue
Do Until pdfjob.cCountOfPrintjobs = i
    DoEvents
Loop

pdfjob.cCombineAll
pdfjob.cPrinterStop = False

'Wait until the file shows up before closing PDF Creator
Do
    DoEvents
Loop Until Dir(outputPDFPath & outputPDFName) = outputPDFName

pdfjob.cClose
Set pdfjob = Nothing

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Sheets("Instructions_Input").Select

End Sub
 
Can you run this in your workbook and paste the results from the Immediate window?

Code:
Sub ken()  
  Dim outputPDFName As String
  Dim outputPDFPath As String
  
  '/// Change the output file name here! ///
  outputPDFName = "West Area with Regions Week " & Sheets("Instructions_Input").Range("D5").Value & ".PDF"
  outputPDFPath = ActiveWorkbook.Path & Application.PathSeparator
  
  Debug.Print outputPDFPath & outputPDFName, Dir(outputPDFPath & outputPDFName)
End Sub
 
C:\Users\DMAM34\Documents\West Area with Regions Week 7.PDF West Area with Regions Week 7.pdf
 
Thanks, I found the problem after I posted that. I had .PDF (in caps) in the outputPDFName and it was saving it as .pdf (without caps). As soon as I changed that, the macro worked.
 
So, it looks like you should do when Dir() = "" or when Len(Dir()) = 0 or use the FSO method. One of the 2nd parts of the Debug.Print is what you should use in your loop.
Code:
Sub ken()  Dim outputPDFName As String
  Dim outputPDFPath As String
  Dim fso As Object
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  
  '/// Change the output file name here! ///
  outputPDFName = "West Area with Regions Week 7.PDF West Area with Regions Week 77.pdf"
  outputPDFPath = "x:\pdf\"
  
  Debug.Print outputPDFPath & outputPDFName, fso.FileExists(outputPDFPath & outputPDFName)
  Debug.Print vbCrLf
  Debug.Print outputPDFPath & outputPDFName, Dir(outputPDFPath & outputPDFName) <> ""
  Debug.Print outputPDFPath & outputPDFName, Len(Dir(outputPDFPath & outputPDFName)) > 0
End Sub

Right, these methods avoids cap issues. That was my other thought. Of course one can use lcase() or ucase() or set an Option to ignore case but I seldom use that.

I would recommend not using .Select though. You can reference the WorkSheets() or Sheets() to point to the right objects. e.g.
Code:
'[COLOR=#333333]Sheets("Regional").Select
[/COLOR][COLOR=#333333]'ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
[/COLOR]WorkSheets("Regional").PrintOut copies:=1, ActivePrint:="PDFCreator"
 
Last edited:
Hello Ken,

I am using your code to combine already existing pdf files from a folder into one file. After reading through your other threads I have installed PDFCreator (PDFSam somehow doesn't work).
I am glad I came across your thread.


I am facing some problems:
1. At oPDF.PrintFile v, it keeps the adobe reader open. When I close it then the control goes to the next line. The same is repeated for the second file as well
2. At the end when the files are merged, it again opens the merged file

How can your code be modified so as it does the execution silently and when the merged file exists it just overwrites it?

Could you please guide and assist in the code? Appreciate your help

Thanks & best regards
Don
 
In the past, the code that I posted worked for me. In v2.3.2, I modified my routine and added some more error checks and features. I added a 5 second delay. It may well run ok for a shorter delay. Since I solved this in another forum, I thought that I would update this thread as well.
Code:
Sub Test_PDFCreatorCombine()  Dim fn(0 To 1) As String, s As String


  fn(0) = ThisWorkbook.Path & "\P1.pdf"
  fn(1) = ThisWorkbook.Path & "\P2.pdf"
  s = ThisWorkbook.Path & "\PDFCreatorCombined.pdf"
  
  PDFCreatorCombine fn(), s
   
  If vbYes = MsgBox(s, vbYesNo + vbQuestion, "Open?") Then Shell ("cmd /c " & """" & s & """")
End Sub


' Older version examples by Ken Puls, http://www.excelguru.ca/content.php?161
 
 ' Macro Purpose: Print to PDF file using PDFCreator v2.3.2, Kenneth Hobson, Oct. 8, 2016
 ' (Download from http://sourceforge.net/projects/pdfcreator/)
 ' COM interface, http://www.pdfforge.org/pdfcreator/manual/com-interface
 ' v 2.3 Manual, http://docs.pdfforge.org/pdfcreator/2.3/en/
 ' Designed for early bind, set reference to: PDFCreator - Your OpenSource PDF Solution
 ' sPDFName() assumed to be 0 index based string array.
Sub PDFCreatorCombine(sPDFName() As String, sMergedPDFname As String, _
  Optional tfKillMergedFile As Boolean = True)
  Dim oPDF As PDFCreator.PdfCreatorObj, q As PDFCreator.Queue
  Dim pj As PrintJob
  Dim i As Integer, ii As Integer
  Dim fso As Object, tf As Boolean
  Dim s() As String
   
  On Error GoTo EndSub
  Set fso = CreateObject("Scripting.FileSystemObject")
  If tfKillMergedFile And fso.FileExists(sMergedPDFname) Then Kill sMergedPDFname
  
  For i = 0 To UBound(sPDFName)
    If fso.FileExists(sPDFName(i)) Then
      ii = ii + 1
      ReDim Preserve s(1 To ii)
      s(ii) = sPDFName(i)
    End If
  Next i
   
  Set q = New PDFCreator.Queue
    With q
      .Initialize
       
      Set oPDF = New PDFCreator.PdfCreatorObj 'PDFCreator.clsPDFCreator
      tf = .WaitForJobs(ii, 5)  'Wait 5 seconds for jobs to que
      
      For i = 1 To UBound(s)
        oPDF.AddFileToQueue s(i)
      Next i


      .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
   End With
EndSub:
  If Not q Is Nothing Then q.ReleaseCom
End Sub
 
In the past, the code that I posted worked for me. In v2.3.2, I modified my routine and added some more error checks and features. I added a 5 second delay. It may well run ok for a shorter delay. Since I solved this in another forum, I thought that I would update this thread as well.

This was really awesome - thanks so much Ken.

I had tinkered a bit with your previous version to overcome an error I was getting with the Com not always releasing (especially if I went to break mode).

I added the below just to help me with debugging (and of course for when a user clicks a button and... something... happens.

Code:
'Original
Set q = New PDFCreator.Queue    With q
'New
startPDFStuff:
          On Error GoTo helpmenow
'Original
    .Initialize
'New
    GoTo finish
helpmenow:
    .ReleaseCom
    GoTo startPDFStuff
finish:
 
Back
Top