Print Embedded PDF to Specific Printer

[Syrathos]

Hi Everyone,

I have come across the below thread which has the relevant code (which works) to open a PDF which has been added to an excel spreadsheet (as an object) and print to the default printer.

Need to Print Embedded PDFs

From my understanding the /t prints to the default printer. I have been reading up and it appears possible to be able to specify a specific printer when using the /t but I am unable to get it to work with below code from the above thread.

Sub PrintEmbeddedPDFs_03()
' ZVI:2013-08-01 http://www.mrexcel.com/forum/excel-questions/717204-need-print-embedded-pdfs-please-help.html
' ZVI:2014-05-14 Added subrotines for printing sheets and embedded PDFs
' ZVI:2014-06-28 Fixed the incorrect finding of last "%%EOF" in PDF with comments
 
  Dim a() As Byte, b() As Byte, i As Long, j As Long, k As Long, n As Long
  Dim FN As Integer, f As String, p As Variant, obj As OLEObject
  Dim PDFPrint As String
  
  PDFPrint = "PDFCreator"
  
  p = ActiveWorkbook.Path
 
  ' Print all PDFs embedded into the active sheet
  For Each obj In ActiveSheet.OLEObjects
    i = 0:  hwnd = 0: Size = 0: Ptr = 0
    If obj.progID Like "Acro*.Document*" And obj.OLEType = 1 Then
      obj.Copy
      If OpenClipboard(0) Then
        hwnd = GetClipboardData(49156)
        If hwnd Then Size = GlobalSize(hwnd)
        If Size Then Ptr = GlobalLock(hwnd)
        If Ptr Then
          ReDim a(1 To CLng(Size))
          CopyMemory a(1), ByVal Ptr, Size
          Call GlobalUnlock(hwnd)
          i = InStrB(a, StrConv("%PDF", vbFromUnicode))
          If i Then
            ' --> ZVI:2014-06-28
             k = InStrB(i, a, StrConv("%%EOF", vbFromUnicode))
            While k
              j = k - i + 7
              k = InStrB(k + 5, a, StrConv("%%EOF", vbFromUnicode))
            Wend
            ' <--
            ReDim b(1 To j)
            For k = 1 To j
              b(k) = a(i + k - 1)
            Next
            Ptr = 0
          End If
        End If
        Application.CutCopyMode = False
        CloseClipboard
        If i Then
          n = n + 1
          f = p & "\_printed_.pdf"
          If Len(Dir(f)) Then Kill f
          FN = FreeFile
          Open f For Binary As #FN
          Put #FN, , b
          Close #FN
            CreateObject("wscript.shell").Run "AcroRd32.exe /N /T """ & f & """", , True
        Kill f
        End If
      Else
        Application.CutCopyMode = False
      End If
    End If
  Next
  
  ' Inform how many Embedded PDFs were printed
  MsgBox "Amount of the printed PDFs: " & n, vbInformation, "PrintEmbeddedPDFs"
 
exit_:
 
  If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number
 
End Sub

I previously had code to change the default printer in windows, but the code itself is too slow (trying to find the default printer before changing, changing, and then changing back). So I was hoping to pass the printer name through to Adobe as it is executing.

Thanks in advance.

 

[John_w]

Put this in a new module, or insert it at the top of the existing module:

#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

Private Const SW_HIDE As Long = 0&

Public Sub ShellExecute_Print(file As String, Optional printerName As String)
    If printerName = "" Then
        ShellExecute Application.hwnd, "Print", file, vbNullString, 0&, SW_HIDE
    Else
        ShellExecute Application.hwnd, "Print", file, Chr(34) & printerName & Chr(34), 0&, SW_HIDE
    End If
End Sub

and replace your line:

CreateObject("wscript.shell").Run "AcroRd32.exe /N /T """ & f & """", , True

with:

ShellExecute_Print f, "Your Printer Name"

 

[Syrathos]

Thanks for your time and response John_w,

I followed your recommendation and when Adobe opens it says the file cannot be found. I tried it with """ & f & """ in keeping with the previous code, but then adobe doesn't open/no error (so no printing).

 

[John_w]

Delete the Kill f line which follows the print line. That deletes the file before Abobe has spooled it to the printer, hence the error.

 

[Syrathos]

Thanks John_w.

Sorry for not responding sooner - when I remove the Kill f line Adobe opens, and the document prints, but it prints to the computer default and not the one specified in the code. In what format does the printer name need to be specified? I have tried ShellExecute_Print f, "PDF Creator" and I have tried ShellExecute_Print f, PDFPrint where I have set PDFPrint as "PDFCreator".

 

[John_w]

Try changing the "Print" on the ShellExecute call to "PrintTo", like this:

Public Sub ShellExecute_Print(file As String, Optional printerName As String)
    If printerName = "" Then
        ShellExecute Application.hwnd, "PrintTo", file, vbNullString, 0&, SW_HIDE
    Else
        ShellExecute Application.hwnd, "PrintTo", file, Chr(34) & printerName & Chr(34), 0&, SW_HIDE
    End If
End Sub

I have tried ShellExecute_Print f, "PDF Creator" and I have tried ShellExecute_Print f, PDFPrint where I have set PDFPrint as "PDFCreator".

You can specify a literal string or a string variable as the 2nd argument to ShellExecute_Print. But note that "PDF Creator" and "PDFCreator" are different. Whichever is correct, is it a physical printer or a virtual printer which generates a PDF file? If the latter, then I don't understand why you're specifying it because the code already creates a PDF file, named "_printed_.pdf".

The printer name is simply the Windows printer name.

 

[Syrathos]

Thanks John_w!

My mistake with the PDF Creator/PDFCreator part, I should have typed it with no space in the forum post. It is a virtual printer which creates PDF files and the reason for this is that the user is completing an excel form and attaching supporting documentation in PDF or Word, and then clicks a button which executes the macro to print the completed form, and the embedded PDF(s) and/or Word(s) each to the PDFCreator so that it can be merged into one document.

I changed the "Print" to "PrintTo" and it works now!

Thanks so much!