Page 2 of 2 FirstFirst 1 2
Results 11 to 15 of 15

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

  1. #11


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #12
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    136
    Articles
    0
    Excel Version
    365
    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:
    'Sheets("Regional").Select
    'ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
    WorkSheets("Regional").PrintOut copies:=1, ActivePrint:="PDFCreator"
    Last edited by Kenneth Hobson; 2015-07-27 at 06:34 PM.

  3. #13
    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

  4. #14
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    136
    Articles
    0
    Excel Version
    365
    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

  5. #15
    Quote Originally Posted by Kenneth Hobson View Post
    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:

Page 2 of 2 FirstFirst 1 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •