Hi Andy,
Very interesting question and quite a few technical challenges. See the attached file (plus a few dummy data files so the file can be tested as is) which is a modified copy of your file. There are complete instructions in the file at the bottom of Sheet1.
One way to print pdf files is to invoke Acrobat (Reader) from inside Excel. Unfortunately only one copy at a time can be printed. To print multiple copies, the same line must be repeated as required. Acrobat (Reader) prints on the 'Windows Default Printer' by default. Other printers can be invoked if needed.
An working excerpt of the basic code to do the printing is included below. The full code is included in Sub PrintMapsUsingWindowsDefaultPrinter() in Ordinary Code module ModPrintPdfFiles .
NOTE: Due to firewall restrictions replace qqq with < above (all qqq are in comments).
Additional information of note:
How the software works (tested on a single user system):
a. The user 'clicks' a Yellow 'CommandButton' which activates Macro PrintMapsUsingWindowsDefaultPrinter() (in Ordinary code module ModPrintPdfFiles) with the proper options.
b. Each NON-BLANK 'Area' cell is read (along with Quantity, Map File Name, and Pdf File Password if applicable).
c. A new instance of Acrobat (Reader) is opened to print one copy of a file to the 'Windows Default Printer'.
This step is repeated as many times as required, until all copies of a file are printed.
NOTE: The software can be modified to select printers other than the 'Windows Default Printer'.
d. The number of jobs in the Printer 'queue' are monitored, until the queue is empty.
Each time the number of the jobs in the queue changes, a new 30 second TIMEOUT countdown starts.
e. The last instance of 'Acrobat (Reader)' is then closed.
Setting up the software to Test this file 'As Is'.
a. Put this file 'ExcelForumPrintPdfFiles*.xlsm' in any Folder.
b. Create the following folder if it does not exist:
c. Extract the files from file ExcelForumPrintPdfFileDataFiles*.zip into the folder created in the step above:
(1) NoPassword.pdf - One page dummy Pdf file that has no password.
(2) NotPdfFile.xxx - Dummy file that is NOT a Pdf file.
(3) PasswordABC.pdf - One page dummy Pdf file that has password of ABC.
(4) ThreeMegabyteFile.pdf - 44 page Pdf file.
NOTE: Original file was too big for Excelforum (had to substitute a smaller file).
d. Open this file and go to VBA (instructions are below if you don’t know how).
e. Find the location of the Adobe Acrobat (Reader) executable file (using Windows Explorer). A typical location is:
c:\program files (x86)\Adobe\Acrobat Reader DC\reader\acrord32.exe
f. At the top of code module ModPrintPdfFiles modify the value of Constant sGblAcrobatOrReaderPathAndFileNAME (If necessary).
g. Save this file. Setup complete.
Lewis
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks