Print Embedded PDF to Specific Printer

Syrathos

New Member
Joined
Mar 28, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
Put this in a new module, or insert it at the top of the existing module:
VBA Code:
#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:
VBA Code:
ShellExecute_Print f, "Your Printer Name"
 

Syrathos

New Member
Joined
Mar 28, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
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

New Member
Joined
Mar 28, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
Try changing the "Print" on the ShellExecute call to "PrintTo", like this:
VBA Code:
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.
 
Solution

Syrathos

New Member
Joined
Mar 28, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,290
Members
416,962
Latest member
samfuge

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
Top