Avatar of Kevin
Kevin
Flag for United States of America asked on

Excel VBA: Set PDF as printer, set filename, and set to append

We have an excel spreadsheet that runs some automated sorting and printing.  Currently it loops through and selects various ranges on a sheet, and send them to a printer.  The "printouts" of these ranges are never saved per-se, only printed.  So we cannot easily  recreate then later....

What we would like to do is have the Excel VBA:

1. select the PDF driver as the default printer (we have a few different PDF creators...)
2. set the path and filename of the SINGLE file to be created
3. Loop through the process mentioned above and write to the desired PDF - with each print command APPENDING to the existing PDF file.
4. Reset the default printer to what it was before.

or any other way to basically do the same thing.

Thanks everyone, and wishing you all well during these stay-at-home times.

Kevin
Printers and ScannersPDF

Avatar of undefined
Last Comment
Kevin

8/22/2022 - Mon
Louis LIETAER

dim myprinter as string
dim printer_name as sttring
printer_name ="name goes here"

    myprinter = Application.ActivePrinter
    Change_Form.PrintOut Preview:=False, ActivePrinter:=printer_name, PrintToFile:=True, PrToFileName:=PSFileName
    Application.ActivePrinter = myprinter
Kevin

ASKER
Thanks Louis.  Most PDF printers that I know of do not allow configuration to automatically append to a file without user interaction.  Can windows inherently do that somehow?
Louis LIETAER

Hi, you may check the preferences of your pdf printer, but sure it will depend of the driver.

You may find one here : https://pdf.wondershare.com/top-pdf-software/free-pdf-printer.html
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Arana (G.P.)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Kevin

ASKER
Thanks Louis and Arana.  I will investigate!!!