PDF Printing Macro

MikeRob

New Member
Joined
May 8, 2020
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,086
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 printNumber of copies
C:\Temp\PDF1.pdf1
C:\Temp\PDF2.pdf2
C:\Temp\PDF3.pdf3
C:\Temp\PDF4.pdf4

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

MikeRob

New Member
Joined
May 8, 2020
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
May 8, 2020
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,086

ADVERTISEMENT

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.
 
Last edited:

MikeRob

New Member
Joined
May 8, 2020
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
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
 

MikeRob

New Member
Joined
May 8, 2020
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,962
Messages
5,744,057
Members
423,843
Latest member
alex2022

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