PDF Printing Macro

[MikeRob]

I didn't know if this was possible or not so I've come here looking for help. I know it's possible to create a macro before whereas I could do a PDF batch printing file in numerical order of 1 PDF each. Now I'm trying to do something a little bit different.

There's a table I have that I'm trying to mimic to do print automation of these pdfs. Here's a small portion of what I would need a macro to achieve:

Batch Print all of the following from a file folder:

1 copy of PDF1
10 copies of PDF2
28 copies of PDF3
14 copies of PDF4
1 copy of PDF5
1 copy of PDF6
40 copies of PDF7

... and so on in that pdf numerical order.

is this even possible? I would greatly appreciate whoever could provide this solution.

 

[John_w]

Yes, this is possible. Suppose you have a table where the first column is the full path and file name of the PDF file and the second column is the number of copies to print:

PDF file to print	Number of copies
C:\Temp\PDF1.pdf	1
C:\Temp\PDF2.pdf	2
C:\Temp\PDF3.pdf	3
C:\Temp\PDF4.pdf	4

This macro loops through all the rows in the table (the first table on the active sheet) and prints the PDF on the default printer.

Option Explicit

#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 Print_PDFs()

    Dim table As ListObject
    Dim i As Long, n As Long
   
    Set table = ActiveSheet.ListObjects(1)
   
    With table
        For i = 1 To .DataBodyRange.Rows.Count
            For n = 1 To .DataBodyRange(i, 2).Value
                ShellExecute_Print .DataBodyRange(i, 1).Value
            Next
        Next
    End With
   
End Sub


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

You can also specify a named printer, like this:

                ShellExecute_Print .DataBodyRange(i, 1).Value, "Windows Printer Name"

The only drawback with the Shell method of printing is that it leaves an Acrobat Pro/Adobe Reader window open.

 

[MikeRob]

Okay, thank you so much for your help. I'm having trouble getting this to run. Getting a message saying:

Run-time error "9":
Subscript out of Range

looks as though it's referring to this portion of code:

Set table = ActiveSheet.ListObjects(1)

 

[MikeRob]

would it have something to do with the PDF Filenames? I know i was giving an example of PDF1 through whatever but that's not the actual PDF file name, but the numerical order is correct. its actually this:

Cell A1 (which is the heading) - PDF File to Print
Cell A2:

C:\Users\T107737\OneDrive - AC\Desktop\EMS\2020 GSB With Watermark\SB (1).pdf

Cell B! (which is column heading) - Number of Copies
Cell B2 - 5

 

[John_w]

Is the data in a proper Excel table? If not, Insert -> Table, and convert the range to a table. With a table it doesn't matter which rows/columns the data is in.

 

[MikeRob]

Is the data in a proper Excel table? If not, Insert -> Table, and convert the range to a table. With a table it doesn't which rows/columns the data is in.

Yes its in an Excel Table

 

[John_w]

On the active sheet?

 

[MikeRob]

My Apologies again... it was not a table, but I have converted the range to a table, and it works now. Thank you ever so much for your time and effort... greatly appreciated